3

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?

10

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.

7

Try using the MERGE statement from SQL2003 Standard - which is the equivalent of UPDATE..SET..FROM syntax.
Example:

MERGE INTO dbo.t1 AS Target
USING (SELECT a,b,c FROM dbo.t2) AS Source
ON (Target.b = Source.b)
WHEN MATCHED THEN
    UPDATE SET Target.a = Source.a

Note: for iSeries, MERGE requires INTO (credit: Jeff Stevens)

0

So to adjust your UPDATE statements, they should look like this

UPDATE t1 
SET   a =  (SELECT a
              FROM t2
              WHERE t2.b = t.b
           )
WHERE b in (SELECT b
             FROM t2
           )

And the statement for updating multiple columns could look like this:

UPDATE t1 
SET ( x, y, z ) = 
    (SELECT 
      x, y, z
     FROM t2
     WHERE t2.b = t1.b
    )
WHERE b in
    (SELECT
      b
     FROM t2
    )

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.