Q

Updating a table with values from another

I need to join two files on OS/400 and update them. File one, field A is to update File two, field B. This query

returned an error: Keyword INNER not expected. Valid tokens: SET.

UPDATE F03012 inner join F0101                 
ON F03012.AIAN8 = F0101.ABAN8    
SET F03012.AIMAN8 = F0101.ABAN84 
WHERE F0101.ABAT1 = 'C'    
Any help would be appreciated. Thanks.

DBMSs tend to vary in their implementation of the SQL UPDATE statement when it involves updating columns to values found in other tables. Try using a fairly generic correlated sub-query, such as this one:

UPDATE F03012 
  SET F03012.AIMAN8 = (SELECT ABAN84 FROM F0101 WHERE F0101.ABAN8 = F03012.AIAN8)
  WHERE F0101.ABAT1 = 'C'; 

For More Information


This was first published in October 2002

Dig deeper on Oracle and SQL

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

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.

0 comments

Oldest 

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:

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close