Updating the 'VALUE' column using DB2/400

I am using DB2/400 and want to update the 'VALUE' column of TBL1 WITH 'PRICE' of TBL2.

Here's the description of the two tables. 

  
TBL1                         TBL2 
------------               ------------ 
ID   | VALUE               ID  | PRICE 
------------                ----------- 
  1  |   10                 1  |   100 
  2  |   20                 2  |   200 
  3  |   30                 3  |   300 
-------------               ----------- 
  
SQL) 
UPDATE TBL1 SET VALUE=B.PRICE 
FROM TBL1 A INNER JOIN TBL2 B 
ON A.ID=B.ID

When I run the above query, I get the error message: "Column qualifier or table B undefined"

But this query works very well on MS SQL server. Please let me know if this is really impossible.

The support is available, but the syntax is different. Update tbl1 t1 set value = (select price from tbl2 t2 where t2.id = t1.id)

If just a subset of the rows are being updated than you'd have to add the following where clause Where exists (select 'x' from tbl2 t22 where t22.id=t1.id)

==================================
MORE INFORMATION ON THIS TOPIC
==================================

Visit the ITKnowledge Exchange and get answers to your DB2 questions fast.

Check out this Search400.com Featured Topic: Expert advice on DB2

Search400.com's targeted search engine: Get relevant information on DB2/400.

The Best Web Links: Tips, tutorials and more.

This was first published in January 2005

Back to top