UPDATE with LEFT JOIN

[thebatfink]

Hi,

I have a select query..

SELECT [table1].[field1], [table1].[field2]
FROM [table1]
LEFT JOIN [table2]
ON [table1].[field3]=[table2].[field1]
WHERE [table2].[field2] = true and [table2].[field3] = false;

which works fine and returns the results I want.. But I now want to make that into an UPDATE query which updates the selected records. I have tried this but it gives an error..

UPDATE [table1]
SET [table1].[field1] = 'N/A', [table1].[field2] = 'N/A'
FROM [table1]
LEFT JOIN [table2]
ON [table1].[field3]=[table2].[field1]
WHERE [table2].[field2] = true and [table2].[field3] = false;

Could anyone explain what the correct syntax should be? I'm assuming I haven't wrote it correctly, but from what I can find 'googling' it looks like it should to me!

Thanks!

 

[Pat Hartman]

When you don't know the syntax, use the QBE to help you. Paste the first query into the QBE and change the query type to "Update". Choose the fields you want to update and assign the new values. Switch to SQL view to look at the SQL string.

 

[thebatfink]

Hey, thanks very much! That worked

The required SQL was..

UPDATE [table1] 
LEFT JOIN [table2]
ON [table1].[field3]=[table2].[field1]
SET [table1].[field1] = 'N/A', [table1].[field2] = 'N/A'
WHERE ((([table2].field2)=True) AND (([table2].field3)=False));

The brackets around the WHERE statements look a bit excessive, but I can see where I was going wrong now. Thanks for the advice!

 

[Pat Hartman]

The QBE is bracket crazy. It also messes up your neatly formated statements. You can stop this by switching to SQL view once the QBE has helped you with the basic format. If you save the query from that view and never save it from QBE view again, it will leave your formatting and bracketing alone.