MySQL Forums
Forum List  »  Oracle

Re: update with select in MYSQL
Posted by: chris trahey
Date: May 13, 2009 02:51PM

Maybe this has been solved already, but here is a (somewhat) simple solution using INSERT with ON DUPLICATE KEY UPDATE

My example is updating a payments table with a most recent payment_status row id.
Payments table has a unique "id", and a "cur_stat_id" which is what we are trying to populate with the most recent matching record from a related table (payment_status).

INSERT INTO payments (id, cur_stat_id) SELECT id, x.statid FROM payments JOIN (SELECT payment_id, max(id) as statid FROM payment_status GROUP BY payment_id) as x ON payments.id=x.payment_id ON DUPLICATE KEY UPDATE payments.cur_stat_id=x.statid

There may be a somewhat shorter route to this (i.e. only naming the id col in the field list and select clause), but you can catch the idea. Basically, we are inserting a known duplicate key to take advantage of the update capabilities of this construct in tandem with the select capabilities of insert ... select.



Edited 1 time(s). Last edit at 05/13/2009 03:51PM by chris trahey.

Options: ReplyQuote


Subject
Views
Written By
Posted
195539
January 22, 2006 09:51AM
72321
January 25, 2006 07:42AM
65220
January 25, 2006 09:19AM
65268
January 27, 2006 12:44PM
46845
September 27, 2006 09:44PM
Re: update with select in MYSQL
33201
May 13, 2009 02:51PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.