UPDATE syntax with ORDER BY, LIMIT, and multiple tables

Question

Learning SQL, sorry if this is rudimentary. Trying to figure out a working UPDATE solution for the following pseudo-language code:

 UPDATE tableA SET tableA.col1 = '$var' WHERE tableA.user_id = tableB.id AND tableB.username = '$varName' ORDER BY tableA.datetime DESC LIMIT 1 

The above is more like SELECT syntax, but I'm basically trying to update a single column value in the last row of table A, where the username is found in the table name .B (named $ varName) is associated with its identification number in table B.id, which exists as an identifier in tableA.user_id.

Hope this makes sense. I assume some kind of JOIN needed, but the subqueries seem unpleasant for UPDATE . I understand that ORDER BY and LIMIT do not work when several tables are involved in UPDATE ... But I need functionality. Is there any way around this?

A little embarrassed, thanks in advance.

Answer 1

The solution is to embed ORDER BY and LIMIT in the FROM clause as part of the join. This will allow you to first find the exact line to update (ta.id), and then commit the update.

 UPDATE tableA AS target INNER JOIN ( SELECT ta.id FROM tableA AS ta INNER JOIN tableB AS tb ON tb.id = ta.user_id WHERE tb.username = '$varName' ORDER BY ta.datetime DESC LIMIT 1) AS source ON source.id = target.id SET col1 = '$var'; 

Hat advice to Baron Schwartz, aka Xaprb, for an excellent post on this exact topic: http://www.xaprb.com/blog/2006/08/10/how-to-use-order-by-and-limit-on- multi-table-updates-in-mysql /

Answer 2

You can use the following query syntax:

 update work_to_do as target inner join ( select w. client, work_unit from work_to_do as w inner join eligible_client as e on e.client = w.client where processor = 0 order by priority desc limit 10 ) as source on source.client = target.client and source.work_unit = target.work_unit set processor = @process_id; 

This works great.