oracle 使用for循环在oracle sql中更新多行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/21405469/
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
Multiple rows update in oracle sql using for loop
提问by Pulkit Bhatia
the below FOR loop doesn't work. I have two columns PID, PAYMENT in table t1 and table t2. I want to update PAYMENT in table t1 from table t2 where t1.PID=t2.PID
下面的 FOR 循环不起作用。我在表 t1 和表 t2 中有两列 PID、PAYMENT。我想从表 t2 更新表 t1 中的付款，其中 t1.PID=t2.PID
FOR X IN(select paymentterm,pid from temp_project)
LOOP
update project p
set p.paymentterm=temp_project.PID
where p.PID=X.PID;
END LOOP;
commit;
回答by Mureinik
You can achieve this behavior without looping:
您无需循环即可实现此行为：
UPDATE project
SET paymentterm = (SELECT peymentterm
FROM temp_project
WHERE project.pid = temp_project.pid)
WHERE pid IN (SELECT pid FROM temp_project)
回答by David Aldridge
Try:
尝试：
update project p
set paymentterm = (select t.paymentterm
from temp_project tp
where tp.pid = p.pid)
where pid in (select pid from temp_project)
... or, if temp_project.pid is constrained to be unique:
...或者，如果 temp_project.pid 被限制为唯一：
update (select p.pid,
p.paymentterm,
t.paymentterm new_paymentterm
from project p join temp_project t on p.pid = t.pid)
set paymentterm = new_paymentterm;
You might make sure that you're not making changes where none are required with:
您可以确保不会在不需要的地方进行更改：
update (select p.pid,
p.paymentterm,
t.paymentterm new_paymentterm
from project p join temp_project t on p.pid = t.pid
where coalesce(p.paymentterm,-1000) != coalesce(t.paymentterm,-1000))
set paymentterm = new_paymentterm;
(Guessing at -1000 being an impossible value for paymentterm there). This could also be written as a MERGE statement.
（猜测 -1000 是那里的付款期限不可能的值）。这也可以写成 MERGE 语句。
