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)
Related Q&A from Kent Milligan
To solve the SQL error -321 on IBM i6.1, use the new values statement to overcome the error. If you are using an older release, declare a cursor ... Continue Reading
When working with DB2 files with columns that have both short and long names, there is no option choose which column names are returned via ODBC ... Continue Reading
When developing tables in a parent-child relationship, use a primary key and a foreign key along with a unique ID to make your database easier to ... Continue Reading
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.