UPDATE ... FROM ... syntax for iSeries for a MS SQL Server developer When I wanted to update a table to the values from another table in SQL Server I would use following syntax:

UPDATE t1
SET t1.a = t2.a
FROM t1
INNER JOIN t2 ON t2.b = t1.b


What would be iSeries 7 syntax for that?
I don't know about iSeries7 but some DBMS (like Oracle) allow this - as long as t2 (b) is a unique or primary key:

UPDATE 
  ( SELECT t1.a AS t1a, t2.a AS t2a
    FROM t1
      INNER JOIN t2 ON t2.b = t1.b
  ) u
SET
    t1a = t2a ;


Tested at (Oracle 11g) SQL-Fiddle

Other DBMS allow this syntax (like SQL-Server which doesn't make any fuss about the uniqueness, although that is not a good thing*):

WITH u AS
 ( SELECT t1.a AS t1a, t2.a AS t2a
   FROM t1
   INNER JOIN t2 ON t2.b = t1.b
 ) 
UPDATE u
SET
 t1a = t2a ;


Tested at (SQL-Server 2008) SQL-Fiddle

*See the blog article: Let's deprecate UPDATE FROM! where Hugo Kornelis mentions):
 ... SQL Server will happily update the same row over and over again if it matches more than one row in the joined table, with only the result of the last of those updates sticking. ...



You can also check this syntax that doesn't use CTEs or updatable views - and thus will work in almost all DBMS:

UPDATE t1
SET a = 
  ( SELECT t2.a
    FROM t2
    WHERE t2.b = t1.b
  )
WHERE EXISTS
  ( SELECT *
    FROM t2
    WHERE t2.b = t1.b
  ) ;




According to the online manual for DB2 i series7 : UPDATE - and if I read it correctly - only the 3rd way is an option in DB2 for iSeries7. The 1st way will work too if the derived table (u) has been defined as a view.