UPDATE with JOIN: Incorrect syntax near the keyword 'RIGHT' 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&lt;&gt;0 AND tblTid.iTidID Is Null 
AND viewOrderAlla.tblOrder_levdatum&gt;=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.
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 &lt;&gt; 0
    AND tblTid.iTidID IS NULL
    AND viewOrderAlla.tblOrder_levdatum &gt;= DATEADD(month, - 6, GETDATE())