SQL 循环更新数据库记录？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/556552/
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 database records in a loop?
提问by musicking123
declare
begin
for i in (select * from emp)
loop
if i.sal=1300 then
update emp
set sal=13000;
end if;
end loop;
end;
This code is updating all the records with salary 13000.
Instead i want to update records having salary 1300 to the value 13000.
Can you tell where I made a mistake?
I am accesing records using implicit cursor..
for every record i am checking the sal value of that record..
if salary value in a particular record is 1500 i want to update it to 15000..
这段代码正在更新工资为 13000 的所有记录。
相反，我想将工资为 1300 的记录更新为值 13000。
你能告诉我哪里出错了吗？
我正在使用隐式游标访问记录..
对于每条记录，我正在检查该记录的sal值..
如果特定记录中的薪水值为1500，我想将其更新为15000..
回答by Sergio
delete that code and just use:
删除该代码并使用：
update emp set sal = 13000 where sal = 1300
回答by Erich Kitzmueller
Whenever you can do the update with one single statement, you should do that instead of using a loop. You'll get a very huge performance gain that way; or, the other way round, updates in a loop cost you a lot of performance.
每当您可以使用单个语句进行更新时，您应该这样做而不是使用循环。这样你会获得非常巨大的性能提升；或者，反过来说，循环更新会消耗大量性能。
If you really really have to use a loop, of course you need a where condition to make sure you are only updating the record you really want to update. A possible way that always works (even if there is no unique key available) is to use the rowid pseudocolumn:
如果你真的必须使用循环，当然你需要一个 where 条件来确保你只更新你真正想要更新的记录。一种始终有效的可能方法（即使没有可用的唯一键）是使用 rowid 伪列：
begin
for i in (select rowid, emp.* from emp)
loop
if i.sal=1300 then
update emp
set sal=13000
where rowid=i.rowid;
end if;
end loop;
end;
Another possibility would be using an explicit cursor and the "update ... where current of cursorname" syntax.
另一种可能性是使用显式游标和“update ... where current of cursorname”语法。
回答by Quassnoi
This code is updating all the records with salary 13000. Instead i want to update records having salary 1300 to the value 13000.
for every record i am checking the sal value of that record.. if salary value in a particular record is 1500 i want to update it to 15000..
这段代码正在更新工资为 13000 的所有记录。相反，我想将工资为 1300 的记录更新为值 13000。
对于每条记录，我正在检查该记录的 Sal 值。如果特定记录中的薪水值为 1500，我想将其更新为 15000。
So what exactly do you want?
那么你到底想要什么？
You want to update only 1,500 salary, you issue:
您只想更新 1,500 薪水，您发出：
UPDATE emp
SET sal = 15000
WHERE sal = 1500;
You want to increase all salary ten times, you issue:
你想把所有的工资增加十倍，你发出：
UPDATE emp
SET sal = sal * 10;
回答by Andrzej Doyle
You need to put a constraint on your update statement.
您需要对更新语句施加约束。
What you have at the moment will loop through the results rows, and if it finds a row with salary equal to 1300, if then executest he following SQL:
你现在所拥有的将遍历结果行，如果它找到工资等于 1300 的行，则执行以下 SQL：
update emp
set sal=13000;Without the contraint this updates every row.
没有约束，这会更新每一行。
回答by Kumar Sourav
here's a quick solution that helps in removing the spaces (trimming) of a column data based on created date:
这是一个快速解决方案，有助于根据创建日期删除列数据的空格（修剪）：
UPDATE?table_Name SET?column_name?=?LTRIM(RTRIM(column_name))
WHERE EXTEND(dateTime_column,?YEAR?TO?DAY)='2020-01-31' ;
