IF statement UPDATE

[cheeks_m]

Hi first post, so apologies if this is in wrong forum.

I am needing to update a field in one table, where a particular value of another table is TRUE, and update that table based on a common field in both tables.

at the moment my query is:

IF EXISTS (SELECT * FROM _tblSnapShotAUSProfServicesOrderLines_Daily
WHERE _tblSnapShotAUSProfServicesOrderLines_Daily.Produc tRelProductName like 'Prof%')
UPDATE OrderHeader_details
SET ServicesSold = 'Y'
WHERE OrderHeader_details.OrderRef = _tblSnapShotAUSProfServicesOrderLines_Daily.OrderR ef

In its current format, I get the column prefix is incorrect which makes sense sort of.
No matter which way I change this, I cannot get the query to work. I one form, it works but essentially performs two seperate statements, one with the select, and one with the update which unfortunately updates every row in the table.

Any assistance is appreciated.

Mick

[nosepicker]

You need to use the "FROM" clause in your UPDATE statement, either like this:

IF EXISTS (SELECT * FROM _tblSnapShotAUSProfServicesOrderLines_Daily
WHERE _tblSnapShotAUSProfServicesOrderLines_Daily.Produc tRelProductName like 'Prof%')
UPDATE OrderHeader_details
SET ServicesSold = 'Y'
FROM OrderHeader_details, _tblSnapShotAUSProfServicesOrderLines_Daily
WHERE OrderHeader_details.OrderRef = _tblSnapShotAUSProfServicesOrderLines_Daily.OrderR ef

Or like this:

IF EXISTS (SELECT * FROM _tblSnapShotAUSProfServicesOrderLines_Daily
WHERE _tblSnapShotAUSProfServicesOrderLines_Daily.Produc tRelProductName like 'Prof%')
UPDATE OrderHeader_details
SET ServicesSold = 'Y'
FROM OrderHeader_details JOIN _tblSnapShotAUSProfServicesOrderLines_Daily
ON OrderHeader_details.OrderRef = _tblSnapShotAUSProfServicesOrderLines_Daily.OrderR ef