DB2 的 UPDATE sql 中的 INNER JOIN [英] INNER JOIN in UPDATE sql for DB2

问题描述

有没有办法在 DB2 的更新语句中使用连接?

Is there a way to use joins in update statements for DB2?

Google 在这方面真的让我失望了

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%'                             

干杯

推荐答案

你没有说你的目标是什么平台.但是，将表称为文件让我相信您没有在 Linux、UNIX 或 Windows (LUW) 上运行 DB2.

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).

但是，如果您在使用 DB2 LUW，请参阅 MERGE 语句:

However, if you are on DB2 LUW, see the MERGE statement:

对于您的示例语句，这将被写为:

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;

请注意:对于 DB2，SUBSTR 函数的第三个参数是要返回的字节数，而不是结束位置.因此，SUBSTR(a.firstfield,10,20) 返回 CHAR(20).但是，SUBSTR(b.anotherfield,1,10) 返回 CHAR(10).我不确定这是否是故意的，但这可能会影响您的比较.

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 的 UPDATE sql 中的 INNER JOIN的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！