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
- Dozens more answers to tough SQL questions from Jason Law are available here.
- The Best SQL Web Links: tips, tutorials, scripts, and more.
- Have an SQL tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize. Submit your tip today!
- Ask your technical SQL questions -- or help out your peers by answering them -- in our live discussion forums.
- Ask the Experts yourself: Our SQL, database design, Oracle, SQL Server, DB2, metadata, object-oriented and data warehousing gurus are waiting to answer your toughest questions.