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...." --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
