SQL oracle如何更新多行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/28608776/
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
how to update multiple rows in oracle
提问by srk786
I would like to update multiple rows with different values for all different records, but don't have any idea how to do that, i am using below sql to update for single record but i have 200 plus records to update
我想为所有不同的记录更新具有不同值的多行，但不知道该怎么做，我正在使用下面的 sql 更新单个记录，但我有 200 多条记录要更新
update employee
set staff_no = 'ab123'
where depno = 1
i have 50 dep and within those dep i need to update 200 plus staff no. any idea. At the moment if i just do a
我有 50 个 dep，在这些 dep 中，我需要更新 200 多个员工编号。任何的想法。目前如果我只是做一个
select * from Departments
i can see list of all employee which needs staff no updating.
我可以看到所有需要员工无需更新的员工列表。
UPDATE person
SET staff_no =
CASE person_no
WHEN 112 THEN 'ab123'
WHEN 223 THEN 'ab324'
WHEN 2343 THEN 'asb324'
and so on.....
END
回答by dasblinkenlight
You should be able to use MERGEstatement to do it in a single shot. However, the statement is going to be rather large:
您应该能够使用MERGEstatement 一次性完成。但是，该语句将相当大：
MERGE INTO employee e
USING (
SELECT 1 as d_id, 'cd234' as staff_no FROM Dual
UNION ALL
SELECT 2 as d_id, 'ef345' as staff_no FROM Dual
UNION ALL
SELECT 3 as d_id, 'fg456' as staff_no FROM Dual
UNION ALL
... -- More selects go here
SELECT 200 as d_id, 'za978' as staff_no FROM Dual
) s
ON (e.depno = S.d_id)
WHEN MATCHED THEN UPDATE SET e.staff_no= s.staff_no
回答by Lalit Kumar B
For conditional update, you could use multiple update statements, or use CASEexpression in the SET clause.
对于条件更新，您可以使用多个更新语句，或者在SET 子句中使用CASE表达式。
Something like,
就像是，
UPDATE table
SET schema.column = CASE
WHEN column1= 'value1' AND column2='value2' THEN
'Y'
ELSE
'N'
END
I wish you tried to search for a similar question on this site, there was a recent question and thiswas my answer.
我希望你试着在这个网站上搜索一个类似的问题，最近有一个问题，这是我的回答。
回答by collapsar
use a case expression
使用 case 表达式
UPDATE employee
SET staff_no =
CASE depno
WHEN 1 THEN 'ab123'
WHEN 2 THEN 'ab321'
--...
ELSE staff_no
END
WHERE depno IN ( 1, 2 ) -- list all cases here. use a subquery if you don't want to / cannot enumerate
回答by Upendra Hukeri
If you have two tables like:
如果您有两个表，例如：
CREATE TABLE test_tab_1 (id NUMBER, name VARCHAR2(25));
CREATE TABLE test_tab_2 (id NUMBER, name VARCHAR2(25));
You can use UPDATEstatement as below:
您可以使用UPDATE如下语句：
UPDATE test_tab_1
SET test_tab_1.name = (SELECT test_tab_2.name FROM test_tab_2
WHERE test_tab_1.id = test_tab_2.id);
