|
Buy Support
Incidents |
|
If you can't find your answer
in the FREE PUBLIC QDeveloper Forum, require URGENT Priority Support, or you need to send us
private or confidential information: |
|
Click Here
|
If you can't
login and post questions or you are having trouble viewing forum posts:
Click Here
|
Callback
Support |
|
If you live in USA, UK, Canada, Australia or New
Zealand, you can leave us details on your question and request us to call you back and discuss
them with you personally (charges apply). |
|
Click Here
|
Buy Support
Incidents |
|
If you can't find your answer
in the FREE PUBLIC QDeveloper Forum, require URGENT Priority Support, or you need to send us
private or confidential information: |
|
Click Here
|
|
| Error: Expected lexical element not found using INNER JOIN in UPDATE |
| Author |
Message |
|
|
| Posted : 2006-12-09 09:04:06 |
We use a custom field in Accounts Receivable to identify customers and invoices with one of the schools we run. Each invoice should be assigned to the school that the customer is assigned to, but sometimes the person keying the invoices misses one. I've written this query to update the invoices School field to match the School field of the customer.
UPDATE "Invoice" INNER JOIN "Customer" ON "Invoice"."CustomerRefListID" = "Customer"."ListID"
SET "Invoice"."CustomFieldSchool" = "Customer"."CustomFieldSchool"
WHERE "Invoice"."CustomFieldSchool" is Null
However, I get the error message "[QODBC] Expected lexical elemtn not found: Set (#11015)"
What am I doing wrong here?
Pat |
|
|
|
| Tom |
 |
| Group | : Administrator |
| Posts | : 5510 |
| Joined | : 2006-02-17 |
|
| Profile |
|
| Posted : 2006-12-09 10:28:32 |
Sorry, QODBC doesn't support the use of INNER JOINS or subqueries within updates. Use this query to locate the records to be updated first:
SELECT * from Invoice where CustomFieldSchool is Null
and then update each customer with NULLs using:
UPDATE Invoice SET CustomFieldSchool = '1234-5678' where CustomerRefListID='F0A-1197683921'
But don't try this with QODBC v7.00.00.194, 7.00.00.199 or 7.00.00.200, please use QODBC v6 or wait until QODBC v7.00.00.201 or higher is available as there is currently a passthrough flag bug that updates all records within a table. |
|
|
|
|
|
| Posted : 2006-12-12 08:09:00 |
Just so I understand this clearly - what you're saying is that there is currently a bug in 7.00.00.200 and earlier versions of 7 that causes all records in a table to be updated when I run a passthrough UPDATE query even though the WHERE clause should exclude some records from the update?
Do you expect to support INNER JOINS in UPDATE queries in a version coming shortly?
Also, the query does seem to work quite well if I don't set it up as a pass through query, though it's very slow:
UPDATE Invoice INNER JOIN Customer ON Invoice.CustomerRefListID = Customer.ListID SET Invoice.CustomFieldSchool = Customer.CustomFieldSchool
WHERE (((Invoice.CustomFieldSchool) Is Null));
Pat |
|
|
|
| Tom |
| |
| Group | : Administrator |
| Posts | : 5510 |
| Joined | : 2006-02-17 |
|
| Profile |
|
| Posted : 2006-12-12 09:23:23 |
Correct. There are currently no plans to support INNER JOINs within UPDATES. We support INNER JOINs within SELECTs:
Select * from Invoice INNER JOIN Customer ON Invoice.CustomerRefListID = Customer.ListID
WHERE (((Invoice.CustomFieldContract) Is Null))
or:
SELECT * FROM {OJ Invoice INNER JOIN Customer ON
(Invoice.CustomerRefListID = Customer.ListID)}
WHERE (((Invoice.CustomFieldContract) Is Null))
or even the more traditional:
Select * from Invoice, Customer
Where Invoice.CustomerRefListID = Customer.ListID
and (((Invoice.CustomFieldContract) Is Null))
QODBC doesn't support the use of INNER JOINS or subqueries within updates. The Microsoft Access Jet Engine is processing your query, but as you already know, the statement itself isn't supported within the driver directly. |
|
|
|
|