SQL Server - inner join when updating

sql-server inner-join
LeeTee picture LeeTee · Mar 6, 2012 · Viewed 800.8k times · Source

I have the below query which does not work. What am I doing wrong? Is this even possible?

UPDATE ProductReviews AS R 
   INNER JOIN products AS P 
       ON R.pid = P.id 
SET R.status = '0' 
WHERE R.id = '17190' 
  AND P.shopkeeper = '89137'

Answer

Aaron Bertrand picture Aaron Bertrand · Mar 6, 2012 
UPDATE R 
SET R.status = '0' 
FROM dbo.ProductReviews AS R
INNER JOIN dbo.products AS P 
       ON R.pid = P.id 
WHERE R.id = '17190' 
  AND P.shopkeeper = '89137';

Related questions

How to Delete using INNER JOIN with SQL Server?

I want to delete using INNER JOIN in SQL Server 2008. But I get this error: Msg 156, Level 15, State 1, Line 15 Incorrect syntax near the keyword 'INNER'. My code: DELETE FROM WorkRecord2 INNER JOIN Employee ON EmployeeRun=EmployeeNo WHERE Company = '1' …

Read More
Difference between JOIN and INNER JOIN

Both these joins will give me the same results: SELECT * FROM table JOIN otherTable ON table.ID = otherTable.FK vs SELECT * FROM table INNER JOIN otherTable ON table.ID = otherTable.FK Is there any difference between the statements in performance …

Read More
Update Query with INNER JOIN between tables in 2 different databases on 1 server

Need some SQL syntax help :-) Both databases are on the same server db1 = DHE db2 = DHE_Import UPDATE DHE.dbo.tblAccounts INNER JOIN DHE_Import.dbo.tblSalesRepsAccountsLink ON DHE.dbo.tblAccounts.AccountCode = DHE_Import.tblSalesRepsAccountsLink.AccountCode SET DHE.dbo.…

Read More