434

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'
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
LeeTee
  • 6,401
  • 16
  • 79
  • 139
  • UPDATE dbo.ProductReviews AS R SET R.status = '0' INNER JOIN dbo.products AS P ON R.pid = P.id WHERE R.id = '17190' AND P.shopkeeper = '89137'; – Hassan Aug 24 '14 at 13:05
  • 2
    This is not a duplicate because he is not trying to update one table FROM another. He is only joining to enforce the where clause. – nuander Oct 02 '17 at 22:41

2 Answers2

917
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';
Aaron Bertrand
  • 272,866
  • 37
  • 466
  • 490
  • can you please explain why syntax alike UPDATE JOIN SET WHERE is incorrect, but it works with UDPATE SET FROM JOIN WHERE? Does it depend on the version of mysql? – Farside Apr 28 '16 at 12:45
  • 14
    @Farside this syntax is for SQL Server. Someone else will have to help you with MySQL syntax, sorry. – Aaron Bertrand Apr 28 '16 at 13:37
74

This should do it:

UPDATE ProductReviews
SET    ProductReviews.status = '0'
FROM   ProductReviews
       INNER JOIN products
         ON ProductReviews.pid = products.id
WHERE  ProductReviews.id = '17190'
       AND products.shopkeeper = '89137'
Bridge
  • 29,818
  • 9
  • 60
  • 82