oracle SQL 与...更新
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/17302824/
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
SQL With... Update
提问by Racso
Is there any way to do some kind of "WITH...UPDATE" action on SQL?
有没有办法WITH...UPDATE对 SQL执行某种“ ”操作？
For example:
例如：
WITH changes AS
(...)
UPDATE table
SET id = changes.target
FROM table INNER JOIN changes ON table.id = changes.base
WHERE table.id = changes.base;
Some context information: What I'm trying to do is to generate a base/targetlist from a table and then use it to change values in another table (changing values equal to baseinto target)
一些上下文信息：我想要做的是从一个表中生成一个base/target列表，然后用它来改变另一个表中的值（改变值等于baseinto target）
Thanks!
谢谢！
回答by Alex Poole
You can use merge, with the equivalent of your withclause as the usingclause, but because you're updating the field you're joining on you need to do a bit more work; this:
您可以使用merge, 与您的with子句等效为using子句，但是因为您正在更新要加入的字段，所以需要做更多的工作；这个：
merge into t42
using (
select 1 as base, 10 as target
from dual
) changes
on (t42.id = changes.base)
when matched then
update set t42.id = changes.target;
.. gives error:
.. 给出错误：
ORA-38104: Columns referenced in the ON Clause cannot be updated: "T42"."ID"
Of course, it depends a bit what you're doing in the CTE, but as long as you can join to your table withint that to get the rowidyou can use that for the onclause instead:
当然，这在一定程度上取决于您在 CTE 中所做的事情，但是只要您可以加入到您的表内，rowid您就可以将其用于on子句：
merge into t42
using (
select t42.id as base, t42.id * 10 as target, t42.rowid as r_id
from t42
where id in (1, 2)
) changes
on (t42.rowid = changes.r_id)
when matched then
update set t42.id = changes.target;
If I create my t42table with an idcolumn and have rows with values 1, 2 and 3, this will update the first two to 10 and 20, and leave the third one alone.
如果我t42用一id列创建我的表，并且有值为 1、2 和 3 的行，这会将前两个更新为 10 和 20，并保留第三个。
It doesn't have to be rowid, it can be a real column if it uniquely identifies the row; normally that would be an id, which would normally never change (as a primary key), you just can't use it and update it at the same time.
它不一定是rowid，如果它唯一标识行，它可以是一个真正的列；通常这将是一个id，它通常永远不会改变（作为主键），你只是不能同时使用它和更新它。
