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...."
