I need to update a field of a table that is itself used in the query to determine what to update. The only error I get out of SQL is a general syntax error.
UPDATE tblInvoice_Master SET tblInvoice_Master.PercentS
SELECT
TaxRate
FROM
tblCaliforniaTaxRate
WHERE
(City = (
SELECT
tblAddress.City
FROM
tblInvoice_Master AS M
INNER JOIN
tblAddress ON M.fkBillingAddress = tblAddress.ID AND
M.fkBillingAddress = tblAddress.ID
WHERE
(M.ID = 622)
)
)
I need to update the tax rate for each record in tblInvoice_Master where M.ID = tblInvoice_Master.ID. I used the value 622 to test and make sure that my subquery is returning only one result. Does anyone have any suggestions. I'm clearly making this more complex than it needs to be.
This Question has been solved and asker verified All Experts Exchange premium technology solutions are available to subscription members.
Experts Exchange has been collecting answers to technology questions since 1996…3 million and counting! If you have a question, chances are we already have your answer.
If you can't find the exact answer you're looking for, ask our exclusive community of 50,000 experts. You’ll get a personalized answer from a trusted professional.
Thousands of free tech tips, tricks, how-to’s and tutorials are available in our peer reviewed articles section. See for yourself how smart our experts are, no login required.
Save solutions to your questions, answers you’ve discovered through searching plus helpful articles in your personal knowledgebase for easy future access.
Access the answers to your technology questions today.
30-day free trial. Register in 60 seconds.
Members of the expert community talk about why the experience at Experts Exchange is different than what you will find anywhere else.
Try it out and discover for yourself.
30-day free trial. Register in 60 seconds.
Join the community of experts here and help other tech pros by answering question in your area of expertise. You can earn FREE access to all Experts Exchange's premium features and resources.
I have no idea what you are doing, but the following will correct your syntax error:
UPDATE tblInvoice_Master
SET tblInvoice_Master.PercentS
(SELECT TaxRate
FROM tblCaliforniaTaxRate
WHERE City = (
SELECT tblAddress.City
FROM tblInvoice_Master AS M
INNER JOIN tblAddress ON M.fkBillingAddress = tblAddress.ID
AND M.fkBillingAddress = tblAddress.ID
WHERE M.ID = 622
)
)
Business Accounts
Answer for Membership
by: pinaldavePosted on 2006-11-11 at 16:24:46ID: 17923365
UPDATE tblInvoice_Master SET tblInvoice_Master.PercentS
SELECT TOP 1
TaxRate
FROM
tblCaliforniaTaxRate
WHERE
(City = (
SELECT
tblAddress.City
FROM
tblInvoice_Master AS M
INNER JOIN
tblAddress ON M.fkBillingAddress = tblAddress.ID AND
M.fkBillingAddress = tblAddress.ID
WHERE
(M.ID = 622)
)
)