ORDER BY、LIMIT 和多个表的 UPDATE 语法 [英] UPDATE Syntax with ORDER BY, LIMIT and Multiple Tables

问题描述

正在学习 SQL，如果这是初级的，请见谅.试图为以下伪代码找出一个有效的 UPDATE 解决方案:

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

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

上面更像是 SELECT 语法，但基本上是在尝试更新 tableA 的 latest row 中的单个列值，其中在 tableB.username 中找到用户名(由$varName表示)链接到它在tableB.id中的ID号，它作为id在tableA.user_id中存在.

The above is more like SELECT syntax, but am basically trying to update a single column value in the latest row of tableA, where a username found in tableB.username (represented by $varName) is linked to its ID number in tableB.id, which exists as the id in tableA.user_id.

希望这是有道理的.我猜想某种JOIN 是必要的，但子查询对于UPDATE 来说似乎很麻烦.我了解 ORDER BY 和 LIMIT 在 UPDATE 中涉及多个表时是禁区......但我需要这个功能.有没有办法解决这个问题?

Hopefully, that makes sense. I'm guessing some kind of JOIN is necessary, but subqueries seem troublesome for UPDATE. I understand ORDER BY and LIMIT are off limits when multiple tables are involved in UPDATE... But I need the functionality. Is there a way around this?

有点困惑，提前谢谢.

推荐答案

解决方案是将 ORDER BY 和 LIMIT 嵌套在 FROM 子句中作为连接的一部分.这让您首先找到要更新的确切行 (ta.id)，然后提交更新.

The solution is to nest ORDER BY and LIMIT in a FROM clause as part of a join. This let's you find the exact row to be updated (ta.id) first, 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';

向 Baron Schwartz(又名 Xaprb)致敬，感谢他就这个确切主题发表了出色的文章:http://www.xaprb.com/blog/2006/08/10/how-to-use-order-by-and-limit-on-multi-table-updates-in-mysql/

Hat tip to Baron Schwartz, a.k.a. Xaprb, for the 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/

这篇关于ORDER BY、LIMIT 和多个表的 UPDATE 语法的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！