SQL 一次更新多行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6610276/
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
Updating multiple rows at once
提问by Rohita Khatiwada
Is it possible to update many rows at a same time?
是否可以同时更新多行？
Following query returns information of current department, an employee is working on.
以下查询返回当前部门的信息，员工正在工作。
SELECT a.empID, a.deparmentID
FROM customer a
INNER JOIN (
SELECT f.empID, max(f.myDate) md
FROM customer f
GROUP BY f.empID
) z ON z.empID = a.empID AND z.md = a.myDate
For example,following is the sample of my table:
例如，以下是我的表的样本：
empID deparmentID myDate
1 1 2011-01-01
2 1 2011-02-10
3 2 2011-02-19
1 2 2011-03-01
2 3 2011-04-01
3 1 2011-05-10
1 3 2011-06-01
So the above query will return,
所以上面的查询将返回，
empID departmentID
1 3
2 3
3 1
Now based on these return values, I want to update my table at one go.
现在基于这些返回值，我想一次性更新我的表。
Currently I am updating these values one at a time using for loop(very slow in performance),
目前我正在使用 for 循环一次更新这些值（性能非常慢），
my query for updating is :
我的更新查询是：
for row in somerows:
UPDATE facttable SET deparment = row[1] WHERE empID = row[0]
...
but I want to know if it is possible to update all these values at once without using loop.
但我想知道是否可以在不使用循环的情况下一次更新所有这些值。
EDIT:
编辑：
I have a single table. And I need to query the same table. This table does not have relation to any other tables. The table structure is:
我只有一张桌子。我需要查询同一张表。该表与任何其他表没有关系。表结构为：
Table Name : Employee
Fields: EmpID varchar
DeptID varchar
myDate date
采纳答案by ypercube??
Can you try this?
你能试试这个吗？
UPDATE customer c
SET depatmentID =
( SELECT a.deparmentID
FROM customer a
INNER JOIN
( SELECT empID
, max(myDate) AS md
FROM customer
GROUP BY empID
) AS z
ON z.empID = a.empID
AND z.md = a.myDate
WHERE a.empID = c.empID
)
or this:
或这个：
UPDATE customer AS c
SET depatmentID = a.derpmentID
FROM customer a
INNER JOIN
( SELECT empID
, max(myDate) AS md
FROM customer
GROUP BY empID
) AS z
ON z.empID = a.empID
AND z.md = a.myDate
WHERE a.empID = c.empID
回答by Balanivash
回答by Ovais Khatri
This could only be possible if you want to update each with same value, then you could use where clause with IN.
这只有在您想用相同的值更新每个值时才有可能，然后您可以将 where 子句与 IN 一起使用。
