Ask the Expert

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

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to: