Troubleshooting - Correlated subqueries in UPDATE Statement

Problem Description:

I would like to update the salesrep field in my existing invoices. I would like to use the salesrep value that is stored in the Customer file. I would normally do this in SQL using a correlated subquery. When I run the following query: update Invoice set SalesReprefFullName = (select SalesRepRefFullName from Customer where Invoice.CustomerrefFullName = Company.FullName), I get the following error: [QODBC] Expected lexical element not found:

Solution:

Sorry, even your nested SELECT statement is invalid. You always need to reference the TxnID for all update operations you using QODBC. For example for Invoices the update operation would look like this:

update Invoice set SalesRepRefListID='10000-1197757777' where Invoice.TxnID='2B55-1071523067'

Where:

SalesRepRefListID is the List ID for an existing Sales Representative from the SalesRep table.
TxnID is the Transaction ID for the existing Invoice from the Invoice table.