Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

INNER JOIN in UPDATE sql for DB2

Tags:

sql

db2

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

like image 477
Hamish Avatar asked Nov 15 '10 12:11

Hamish


People also ask

Can we use inner join in update statement?

SQL Server UPDATE JOIN syntax To query data from related tables, you often use the join clauses, either inner join or left join. In SQL Server, you can use these join clauses in the UPDATE statement to perform a cross-table update.

What is inner join in db2?

You can request an inner join, by running a SELECT statement in which you specify the tables that you want to join the FROM clause and specify a WHERE clause or an ON clause to indicate the join condition. The join condition can be any simple or compound search condition that does not contain a subquery reference.

Can we update table using join?

SQL UPDATE JOIN could be used to update one table using another table and join condition. UPDATE tablename INNER JOIN tablename ON tablename. columnname = tablename.

Can we join two tables in update query?

' We can update the data of a table using conditions of other joined tables. It is possible to join two or more tables in an UPDATE query.


1 Answers

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.

like image 132
Ian Bjorhovde Avatar answered Sep 21 '22 05:09

Ian Bjorhovde