INNER JOIN in UPDATE sql for DB2

by

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:
update: note that Db2 for IBM i added MERGE support in late 2010 to v7.1 and higher. Db2 for z/OS also has it.

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.

More Related Contents:

  1. DB2 Comma Separated Output by Groups
  2. Does DB2 have an “insert or update” statement?
  3. how to transform comma separated column into multiples rows in db2
  4. Pivoting in DB2
  5. Regular Expressions in DB2 SQL
  6. How to resolve SQL0418N Error
  7. DB2 Query to retrieve all table names for a given schema
  8. How to check db2 version
  9. COUNT MANY FIELDS IN ACCESS [closed]
  10. Max sal to print base on manager_id
  11. Join vs. sub-query
  12. Subqueries vs joins
  13. Create a date from day month and year with T-SQL
  14. What is the expected behaviour for multiple set-returning functions in SELECT clause?
  15. SQL UPDATE SET one column to be equal to a value in a related table referenced by a different column?
  16. SQL: Find the max record per group [duplicate]
  17. SELECT FOR UPDATE with SQL Server
  18. How to group by month from Date field using sql
  19. Group by alias (Oracle)
  20. Why is my t-sql left join not working?
  21. Delete parent if it’s not referenced by any other child
  22. Grouped string aggregation / LISTAGG for SQL Server
  23. Is there a SQL implementation of PBKDF2?
  24. Cross Join without duplicate combinations
  25. Table or column name cannot start with numeric?
  26. How do I add a auto_increment primary key in SQL Server database?
  27. SQL: Alias Column Name for Use in CASE Statement
  28. Select rows with same id but different value in another column
  29. Use one CTE many times
  30. How to store a list in a db column

Leave a Comment