您可以在更新中使用 with 子句;你只需要在正确的地方做:
UPDATE mytable
SET name = (WITH temp AS((SELECT 'abcd' AS oldvalue, 'defg' AS newvalue FROM dual) UNION
(SELECT .....) --About 300 lines of this, copied from Excel and then formatted into the SELECT statement
)
SELECT newvalue
FROM temp
WHERE mytable.name = temp.oldvalue);
但是,您可能只想更新临时子查询中存在的行,因此您需要一个额外的 where 子句:
UPDATE mytable
SET name = (WITH temp AS((SELECT 'abcd' AS oldvalue, 'defg' AS newvalue FROM dual) UNION
(SELECT .....) --About 300 lines of this, copied from Excel and then formatted into the SELECT statement
)
SELECT newvalue
FROM temp
WHERE mytable.name = temp.oldvalue)
WHERE EXISTS (WITH temp AS((SELECT 'abcd' AS oldvalue, 'defg' AS newvalue FROM dual) UNION
(SELECT .....) --About 300 lines of this, copied from Excel and then formatted into the SELECT statement
)
SELECT NULL
FROM temp
WHERE mytable.name = temp.oldvalue);
或者,使用 MERGE 语句:
merge into mytable tgt
using (WITH temp AS((SELECT 'abcd' AS oldvalue, 'defg' AS newvalue FROM dual) UNION
(SELECT .....) --About 300 lines of this, copied from Excel and then formatted into the SELECT statement
)
SELECT mytable.rowid r_id,
temp.newvalue
FROM temp
inner join mytable on mytable.name = temp.oldvalue) src
on (tgt.rowid = src.r_id)
when matched then
update set tgt.name = src.newvalue;
注意:您必须连接到合并语句的源查询中的实际表,因为您正在尝试更新正在连接的列,而您无法在合并语句中执行此操作 - 因此我已将合并连接切换为加入 mytable.rowid。
您必须测试这两个语句,看看哪一个对您的数据性能最佳。