带有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的最新行中的单个列值,该用户名在tableB.username中找到(用$ varName表示) )链接到其tableB.id中的ID号,该ID号作为tableA.user_id中的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';
给Xaprb的Schwarz男爵的帽子提示有关这个确切主题的出色文章: 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屋!