Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
    
Ajeh, please stop removing the DB2 tag. If this is DB2 for iSeries 7, leave the tag alone. –  Aaron Bertrand Jan 23 at 21:45
    
Come and chat in The Heap: there is a breakdown of communication here... –  Jack Douglas Jan 24 at 15:32

3 Answers 3

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.

share|improve this answer

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

MERGE 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
share|improve this answer
up vote -2 down vote accepted

After a little bit of tinkering I figured that difference between MS SQL and iSeries is that iSeries allows to use alias on the table being updated, which can be used in WHERE clause:

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

And the syntax for updating multiple columns is as follows:

UPDATE t1 t
SET (x, y, z) = (
    SELECT t2.x, t2.y, t2.z
    FROM t2
    WHERE t2.b = t.b
)
share|improve this answer
4  
Yes, I'm speaking from experience (in several DBMS). Both your queries look like standard SQL and although they may not run in all DBMS, in those that they will run, they will affect all rows of t1 because there is no WHERE in your UPDATE. That should be true in iSeries, too. –  ypercube Jan 22 at 9:56
2  
In each case your statement is simply UPDATE ... SET ..., not UPDATE ... SET ... WHERE .... Therefore it will affect every row, as it has no filter. (You do have a WHERE clause there but it applies to the subquery. You can't filter the outer query's rows from within a correlated subquery. Well, if iSeries did allow that, that would be very extraordinary. And confusing.) –  Andriy M Jan 22 at 10:38
2  
@ShawnMelton: I hope there are no non-IBM flavours of DB2. It would scare me if there was :) Did you mean AS/400 flavor of DB2? –  ajeh Jan 22 at 16:01
3  
5  
See the online manual for DB2 i series7 : UPDATE where it clearly states: WHERE Specifies the rows to be updated. The clause can be omitted, or a search-condition or cursor-name can be specified. If the clause is omitted, all rows of the table or view are updated. –  ypercube Jan 24 at 15:25

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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