MySQL Forums
Forum List  »  InnoDB

Update with one-to-many join
Posted by: Tyler Hains
Date: May 20, 2005 02:08PM

I'm not quite sure how to handle one-to-many joins in an Update statement. For example, I want to update a table of accounts with the first store where they transacted. The closest I've been able to come up with is this:

Update accounts a left join transactions t on (a.account = t.account)
set a.storecode = t.storecode
where a.storecode is null or a.storecode = ''

This works pretty well, as long as the transactions table is used in chronological order. But, how can I force it to order by trans_id, for instance?


Options: ReplyQuote

Written By
Update with one-to-many join
May 20, 2005 02:08PM

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.