SQL 使用嵌套查询的更新语句
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13881649/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
update statement using nested query
提问by user85116
I have a table:
我有一张桌子：
mytable:
id
userID
logDate
lastLogDate
For every row in that table, I want to update the 'lastLogDate' column to be the max value of logDate on a per user basis...
对于该表中的每一行，我想将“lastLogDate”列更新为每个用户的 logDate 的最大值...
Conceptually, each user should have a lastLogDate = the value that is returned by:
从概念上讲，每个用户都应该有一个 lastLogDate = 返回的值：
select max(logDate) from mytable group by userID
Can somebody help me write the update statement for that?
有人可以帮我写更新声明吗？
回答by DWright
Something like this?
像这样的东西？
UPDATE mytable SET lastLogDate = t.maxDateForUser
FROM
(
SELECT userid, MAX(logDate) as maxDateForUser
FROM mytable
GROUP BY userId
) t
WHERE mytable.userid = t.userid
回答by Mahmoud Gamal
You can do this:
你可以这样做：
UPDATE t
SET t.logDate = t2.LatestDate
FROM YourTable t
INNER JOIN
(
SELECT userID, MAX(LogDate) LatestDate
FROM YourTable
GROUP BY userID
) t2 ON t.userID = t2.userID;
回答by Alf
I don't know if I understood you correctly. Otherwise be a bit more specific, but from what I get, you should do something along the lines of:
不知道我理解对不对。否则更具体一点，但从我得到的信息来看，你应该按照以下方式做一些事情：
UPDATE `mytable`
SET lastLogDate = (SELECT statement goes here)
WHERE ...
回答by rajnish
Following update statement should do what you are looking for
以下更新语句应该做你正在寻找的
update mytable mt set lastLogDate = (select max(logDate) from mytable where userID = mt.userID)
回答by SatyaHarish
UPDATE mytable mT,
(SELECT userid,
MAX(logDate) AS maxDateForUser
FROM mytable
GROUP BY userId) t
SET mT.lastLogDate = t.maxDateForUser
WHERE mT.userid = t.userid;
回答by DBGEEK
you can simply write a nested query like this
您可以简单地编写这样的嵌套查询
Update mytable a
set
a.lastLogDate = (select max(logDate) from mytable b
where a.id=b.id)
Where...;
