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
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.
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.
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.
' 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.
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With