From: Andrei V.Loukinykh
Date: April 13 1999 2:52pm
Subject: Re:conditional replace/insert
List-Archive: http://lists.mysql.com/mysql/1769
Message-Id: <37135A10.77DE6E92@pb301e.crimea.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=koi8-r
Content-Transfer-Encoding: 7bit

Christian Mack wrote:
> 
> "Andrei V.Loukinykh" wrote:
> >
> > Hi
> >  If anybody know how to handle the problem, let me know please.
> >
> >  I have 2 tables with the similar 4 fields.
> >   The first table is temporary( and small) and the second one is big.
> >
> > table TMP                                  table MAIN
> > DATE  DN  ConvTime  DIGITS             DATE  DN  ConvTime DIGITS
> >
> >  I want to add each record from the TMP into the MAIN in condition, that
> > there is no record in MAIN with the same combination of DATE,DN     and
> > DIGITS. Otherwise ( if such a combination already exists) I have to
> > replace existing record with the new one BUT THE FIELD ConvTime MUST BE
> > SUMMARISED.
> > That's all.
> > Thanks in advance.
> 
> Hi Andrei
> 
> If you have an UNIQUE KEY over Date, DN and DIGITS you can use the REPLACE syntax
> like this:
> REPLACE INTO
>         main
>         (Date
>         ,DN
>         ,DIGITS
>         ,ConvTime)
> SELECT
>         tmp.Date
>         ,tmp.DN
>         ,tmp.DIGITS
>         ,IFNULL(main.ConvTime,tmp.ConvTime,tmp.ConvTime + main.ConvTime)

           may be you mean IF ?  ( IFNULL only has 2 parameters)
> FROM
>         tmp
>         LEFT JOIN main
> 
> Tschau
> Christian
  I'm using mysql3.21.33b and when trying to do it,getting the message:
   "Not unigue table/alias: 'main'"
  Should I have both tables to have UNIQUE on these fields?
  I can't make it in TMP ,because it's allowed to have duplicated
records in it.

 My tables:
CREATE TABLE main (id MEDIUMINT NOT NULL AUTO_INCREMENT, date CHAR(8)
NOT NULL,dn CHAR(5) NOT NULL,ConvTime SMALLINT NOT NULL,digits CHAR(5)
NOT NULL,PRIMARY KEY(id),UNIQUE inx(date,dn,digits));

CREATE TABLE tmp (id MEDIUMINT NOT NULL AUTO_INCREMENT, date CHAR(8) NOT
NULL,dn CHAR(5) NOT NULL,ConvTime SMALLINT NOT NULL,digits CHAR(5) NOT
NULL,PRIMARY KEY(id),INDEX inxs(date,dn,digits));

Best regards,
 Andrei

P.S. We have 15 C here (in Evpatoria,Crimea)
     The sea is going to become warm soon.:)