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.:)
