UPDATE with JOIN: Incorrect syntax near the keyword 'RIGHT'

by Henrik Erlandsson   Last Updated September 20, 2018 09:06 AM

I'm using SQL Server Management Studio 2005. I want to look up a column value in another table, using a shared ID tblOrder_ordernr in both tables, but only update one table.

To me, the query is strangely written (UPDATE tblTid but SET viewOrderAlla...), but this is what I have to work with. How would I rewrite this query to do the same?

UPDATE tblTid RIGHT JOIN viewOrderAlla ON tblTid.sOrdernr = viewOrderAlla.tblOrder_ordernr 
SET viewOrderAlla.tblOrder_verkligtid = 0
WHERE viewOrderAlla.tblOrder_verkligtid<>0 AND tblTid.iTidID Is Null 
AND viewOrderAlla.tblOrder_levdatum>=DATEADD(month,-6,GETDATE())

I've tried moving the SET to before the RIGHT JOIN as per some other questions here, but I find no answer that even lets me parse the expression without a syntax error.

The tables are linked into an Access 2013 database, and there, the query runs fine. But not in SQL server.

Answers 1

Try something like this:

UPDATE viewOrderAlla
SET viewOrderAlla.tblOrder_verkligtid = 0
FROM tblTid
RIGHT JOIN viewOrderAlla ON tblTid.sOrdernr = viewOrderAlla.tblOrder_ordernr
WHERE viewOrderAlla.tblOrder_verkligtid <> 0
    AND tblTid.iTidID IS NULL
    AND viewOrderAlla.tblOrder_levdatum >= DATEADD(month, - 6, GETDATE())
Scott Hodgin
Scott Hodgin
September 20, 2018 08:49 AM

Related Questions

Joining multiple tables results in duplicate rows

Updated June 04, 2015 22:02 PM

ON clause of JOIN specific syntax?

Updated February 28, 2016 01:02 AM

Update records based on inserted IDs

Updated August 20, 2015 20:02 PM

SQL Server - Update using Join in Merge

Updated September 02, 2017 10:06 AM