Re: update with select in MYSQL
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.
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.