Ask the Expert

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.

    Requires Free Membership to View

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

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: