Knowledgebase: TroubleShooting
[QODBC-Desktop] Troubleshooting - Correlated subqueries in UPDATE Statement
Posted by Jack - QODBC Support on 30 March 2016 10:16 AM

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.

(0 vote(s))
Helpful
Not helpful

Comments (0)
Post a new comment
 
 
Full Name:
Email:
Comments:
CAPTCHA Verification 
 
Please complete the captcha below (we use this to prevent automated submissions).