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

问题描述

有没有办法在DB2的更新语句中使用联接？

Google真的让我失望了一个

这大概是我想要实现的（...除了明显的工作....）

 更新file1内连接文件2 
在substr（file1.firstfield，10,20）= substr（file2.anotherfield，1,10）
 set file1.firstfield =（'BIT OF TEXT'concat file2。东西）
其中file1.firstfield像'BLAH％'
  

干杯

解决方案

你不说什么平台你的目标。参考表格作为文件，但是，导致我相信你不在Linux，UNIX或Windows（LUW）上运行DB2。

但是，如果你在DB2 LUW上有，请参阅 MERGE 语句：

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

  merge into file1 a 
 using（select anotherfield，something from file2）b 
 on substr（a.firstfield，10 ，20）= substr（b.anotherfield，1,10）
匹配时，a.firstfield像'BLAH％'
然后更新set a.firstfield ='BIT OF TEXT'|| ; 
  

请注意：对于DB2，SUBSTR函数的第三个参数是要返回的字节数，不是结局。因此，SUBSTR（a.firstfield，10,20）返回CHAR（20）。但是，SUBSTR（b.anotherfield，1,10）返回CHAR（10）。我不知道这是否是故意的，但可能会影响您的比较。

Is there a way to use joins in update statements for 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

解决方案

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

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;

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.

这篇关于INNER JOIN UPDATE sql for DB2的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！