List:General Discussion« Previous MessageNext Message »
From:Paul DuBois Date:November 2 2001 4:57pm
Subject:RE: UPDATE table1 FROM table2
View as plain text  
At 9:47 AM -0700 11/2/01, Steve Meyers wrote:
>That's a dangerous solution.  If there are more columns in test2 than ID
>and Value, the REPLACE will delete those values.  As you noted in the
>manual, the old record is deleted before the new record is inserted.
>
>Multi-table updates should happen in 4.1, I think, which is due out
>fairly soon.
>
>If you want to do it Rick's way, try the following:
>
>REPLACE INTO test2 (test2.id, test2.value, test2.other)
>SELECT test1.id, test1.value, test2.other FROM test1, test2 WHERE
>test1.id=1 and test2.id=test1.id

What if there's no existing record to replace?
Won't the SELECT select no rows, with the result that the statement
does nothing?

>
>This will do the same thing, but will keep the value of the "other"
>column.  Repeat for as many columns as necessary.
>
>Also, Rick, isn't it a bit unnecessary to use LIKE in your example?
>
>Steve Meyers
>
>On Fri, 2001-11-02 at 06:50, Rick Emery wrote:
>>  Funny you should ask that.  There was a similar questoin answer on the PH-DB
>>  mailing list (different poster, though)
>>  Answer is:
>>
>>  REPLACE INTO test2 (test2.ID, test2.Value)
>>	SELECT test1.ID, test1.Value FROM test1 WHERE test1.ID LIKE '1'
>>
>>  From manual:
>>  "...REPLACE works exactly like INSERT, except that if an old record in
>>  the table has the same
>>  value as a new record on a unique index, the old record is deleted
>>  before the new record is
>  > inserted...."


Thread
Query mych slower when using index (Innodb)nsabbi31 Oct
  • Query mych slower when using index (Innodb)Michael Widenius31 Oct
    • Re: Query mych slower when using index (Innodb)nsabbi2 Nov
      • UPDATE table1 FROM table2Moshe Gurvich2 Nov
        • UPDATE table1 FROM table2Michael Widenius16 Dec
      • Re: Query mych slower when using index (Innodb)Michael Widenius2 Nov
RE: UPDATE table1 FROM table2Rick Emery2 Nov
  • RE: UPDATE table1 FROM table2Steve Meyers2 Nov
    • RE: UPDATE table1 FROM table2Paul DuBois2 Nov
      • RE: UPDATE table1 FROM table2Steve Meyers2 Nov