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

    Login

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

Join the conversationComment

Share
Comments

    Results

    Contribute to the conversation

    All fields are required. Comments will appear at the bottom of the article.
    Back to top