DB2 的 UPDATE sql 中的 INNER JOIN
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4184209/
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
INNER JOIN in UPDATE sql for DB2
提问by Hamish
Is there a way to use joins in update statements for DB2?
有没有办法在 DB2 的更新语句中使用连接？
Google has really let me down on this one
谷歌真的让我失望了
This is roughly what I'm trying to achieve (... except obviously working ....)
这大致就是我想要实现的（......除了明显有效......）
update file1 inner join file2
on substr(file1.firstfield,10,20) = substr(file2.anotherfield,1,10)
set file1.firstfield = ( 'BIT OF TEXT' concat file2.something )
where file1.firstfield like 'BLAH%'
Cheers
干杯
回答by Ian Bjorhovde
You don't say what platform you're targeting. Referring to tables as files, though, leads me to believe that you're NOT running DB2 on Linux, UNIX or Windows (LUW).
你没有说你的目标平台是什么。但是，将表称为文件，让我相信您不是在 Linux、UNIX 或 Windows (LUW) 上运行 DB2。
However, if you areon DB2 LUW, see the MERGEstatement:
但是，如果您使用的是DB2 LUW，请参阅MERGE语句：
For your example statement, this would be written as:
对于您的示例语句，这将写为：
merge into file1 a
using (select anotherfield, something from file2) b
on substr(a.firstfield,10,20) = substr(b.anotherfield,1,10)
when matched and a.firstfield like 'BLAH%'
then update set a.firstfield = 'BIT OF TEXT' || b.something;
Please note: For DB2, the third argument of the SUBSTR function is the number of bytes to return, not the ending position. Therefore, SUBSTR(a.firstfield,10,20) returns CHAR(20). However, SUBSTR(b.anotherfield,1,10) returns CHAR(10). I'm not sure if this was done on purpose, but it may affect your comparison.
请注意：对于 DB2，SUBSTR 函数的第三个参数是要返回的字节数，而不是结束位置。因此，SUBSTR(a.firstfield,10,20) 返回 CHAR(20)。但是，SUBSTR(b.anotherfield,1,10) 返回 CHAR(10)。我不确定这是不是故意这样做的，但它可能会影响您的比较。
回答by Donnie
Joins in updatestatements are non-standard and not supported by all vendors. What you're trying to do can be accomplished with a sub-select:
update语句中的联接是非标准的，并非所有供应商都支持。您可以通过子选择完成您要执行的操作：
update
file1
set
firstfield = (select 'stuff' concat something from file2 where substr(file1.field1, 10, 20) = substr(file2.xxx,1,10) )
where
file1.foo like 'BLAH%'
回答by eduardo
Try this and then tell me the results:
试试这个，然后告诉我结果：
UPDATE File1 AS B
SET b.campo1 = (SELECT DISTINCT A.campo1
FROM File2 A
INNER JOIN File1
ON A.campo2 = File1.campo2
AND A.campo2 = B.campo2)
回答by Robert Lujo
Update to the answer https://stackoverflow.com/a/4184237/565525:
更新答案https://stackoverflow.com/a/4184237/565525：
if you want multiple columns, that can be achived like this:
如果你想要多列，可以这样实现：
update file1
set
(firstfield, secondfield) = (
select 'stuff' concat 'something from file2',
'some secondfield value'
from file2
where substr(file1.field1, 10, 20) = substr(file2.xxx,1,10) )
where
file1.foo like 'BLAH%'
Source: http://www.dbforums.com/db2/1615011-sql-update-using-join-subquery.html#post6257307
来源：http: //www.dbforums.com/db2/1615011-sql-update-using-join-subquery.html#post6257307
回答by Justin
Here's a good example of something I just got working:
这是我刚刚开始工作的一个很好的例子：
update cac c
set ga_meth_id = (
select cim.ga_meth_id
from cci ci, ccim cim
where ci.cus_id_key_n = cim.cus_id_key_n
and ci.cus_set_c = cim.cus_set_c
and ci.cus_set_c = c.cus_set_c
and ci.cps_key_n = c.cps_key_n
)
where exists (
select 1
from cci ci2, ccim cim2
where ci2.cus_id_key_n = cim2.cus_id_key_n
and ci2.cus_set_c = cim2.cus_set_c
and ci2.cus_set_c = c.cus_set_c
and ci2.cps_key_n = c.cps_key_n
)
回答by Antonio
Just to update only the rows that match the conditions, and avoid updating nulls in the other rows:
只更新符合条件的行，避免更新其他行中的空值：
update table_one set field_1 = 'ACTIVE' where exists
(select 1 from table_two where table_one.customer = table_two.customer);
It works in a DB2/AIX64 9.7.8
它适用于 DB2/AIX64 9.7.8
回答by Esperento57
for you ask
你问
update file1 f1
set file1.firstfield=
(
select 'BIT OF TEXT' || f2.something
from file2 f2
where substr(f1.firstfield,10,20) = substr(f2.anotherfield,1,10)
)
where exists
(
select * from file2 f2
where substr(f1.firstfield,10,20) = substr(f2.anotherfield,1,10)
)
and f1.firstfield like 'BLAH%'
if join give multiple result you can force update like this
如果加入给出多个结果，你可以像这样强制更新
update file1 f1
set file1.firstfield=
(
select 'BIT OF TEXT' || f2.something
from file2 f2
where substr(f1.firstfield,10,20) = substr(f2.anotherfield,1,10)
fetch first rows only
)
where exists
(
select * from file2 f2
where substr(f1.firstfield,10,20) = substr(f2.anotherfield,1,10)
)
and f1.firstfield like 'BLAH%'
template methode
模板方法
update table1 f1
set (f1.field1, f1.field2, f1.field3, f1.field4)=
(
select f2.field1, f2.field2, f2.field3, 'CONSTVALUE'
from table2 f2
where (f1.key1, f1.key2)=(f2.key1, f2.key2)
)
where exists
(
select * from table2 f2
where (f1.key1, f1.key2)=(f2.key1, f2.key2)
)
回答by Leo
The reference documentation for the UPDATE statementon DB2 LUW 9.7 gives the following example:
DB2 LUW 9.7 上UPDATE 语句的参考文档给出了以下示例：
UPDATE (SELECT EMPNO, SALARY, COMM,
AVG(SALARY) OVER (PARTITION BY WORKDEPT),
AVG(COMM) OVER (PARTITION BY WORKDEPT)
FROM EMPLOYEE E) AS E(EMPNO, SALARY, COMM, AVGSAL, AVGCOMM)
SET (SALARY, COMM) = (AVGSAL, AVGCOMM)
WHERE EMPNO = '000120'
The parentheses after UPDATE can contain a full-select, meaning any valid SELECT statement can go there.
UPDATE 后面的括号可以包含全选，这意味着任何有效的 SELECT 语句都可以放在那里。
Based on that, I would suggest the following:
基于此，我建议如下：
UPDATE (
SELECT
f1.firstfield,
f2.anotherfield,
f2.something
FROM file1 f1
WHERE f1.firstfield like 'BLAH%'
INNER JOIN file2 f2
ON substr(f1.firstfield,10,20) = substr(f2.anotherfield,1,10)
)
AS my_files(firstfield, anotherfield, something)
SET
firstfield = ( 'BIT OF TEXT' || something )
Edit: Ian is right. My first instinct was to try subselects instead:
编辑：伊恩是对的。我的第一直觉是尝试子选择：
UPDATE file1 f1
SET f1.firstfield = ( 'BIT OF TEXT' || (
SELECT f2.something
FROM file2 f2
WHERE substr(f1.firstfield,10,20) = substr(f2.anotherfield,1,10)
))
WHERE f1.firstfield LIKE 'BLAH%'
AND substr(f1.firstfield,10,20) IN (
SELECT substr(f2.anotherfield,1,10)
FROM file2 f2
)
But I'm not sure if the concatenation would work. It also assumes that there's a 1:1 mapping between the substrings. If there are multiple rows that match, it wouldn't work.
但我不确定串联是否有效。它还假定子字符串之间存在 1:1 映射。如果有多个匹配的行，它将不起作用。
回答by ConcernedOfTunbridgeWells
In standard SQL this type of update looks like:
在标准 SQL 中，这种类型的更新如下所示：
update a
set a.firstfield ='BIT OF TEXT' + b.something
from file1 a
join file2 b
on substr(a.firstfield,10,20) =
substr(b.anotherfield,1,10)
where a.firstfield like 'BLAH%'
With minor syntactic variations this type of thing will work on Oracle or SQL Server and (although I don't have a DB/2 instance to hand to test) will almost certainly work on DB/2.
通过细微的语法变化，这种类型的东西可以在 Oracle 或 SQL Server 上运行，并且（尽管我没有要测试的 DB/2 实例）几乎肯定可以在 DB/2 上运行。
