Q
Evaluate Weigh the pros and cons of technologies, products and projects you are considering.

Updating fields within tables

How can I update a field in table A from table B, matching both Table A.Field1 and TableB.Field1 in SQL400? I have tried this:

Update lib/pffile1 set balance = (select sum(balance) from lib/pffile2 ) where lib/pffile1.ssn = lib/pffile2.ssn

The problem: I'm getting the message pffile2.ssn is undefined.


I think you want to try something such as the following update: pffile2.ssn can only be referenced on a Select statement where that table is defined on the from clause. Your example fails because pffile2 is only referenced on the from clause of the embedded select statement.

Update pff1 set balance = (select sum(balance) from lib/pffile2) where exists (select 'x' from pff2 where pff2.ssn=pff1.ssn)

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

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

The Best Web Links: tips, tutorials and more.

Check out this recent Search400.com Featured Topic: Ask the DB2 UDB doctor .


Dig Deeper on DB2 UDB (universal databases)

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.

-ADS BY GOOGLE

SearchDataCenter

Close