SQL UPDATE with JOIN

Increase the unit price by 10% for all products that have been sold before.

UPDATE P
   SET P.UnitPrice = P.UnitPrice * 1.1
  FROM Product P
  JOIN OrderItem I ON P.Id = I.ProductId

P and I are table aliases.

JOIN syntax.

UPDATE table-name1
   SET column-name1 = value1, 
       column-name2 = value2, ...
  FROM table-name1 
  JOIN table-name2 ON column-name3 = column-name4
 WHERE condition

INNER JOIN syntax.

UPDATE table-name1
   SET column-name1 = value1, 
       column-name2 = value2, ...
  FROM table-name1 
 INNER JOIN table-name2 ON column-name3 = column-name4
 WHERE condition

JOIN is the same as INNER JOIN; the INNER keyword is optional.
JOIN, or INNER JOIN, is the most commonly used type of JOIN operation.

UPDATE P
   SET IsDiscontinued = 1
  FROM Product P
  LEFT JOIN OrderItem I ON P.Id = I.ProductId
 WHERE I.Id IS NULL

This UPDATE uses a LEFT JOIN operation.

UPDATE I
   SET I.UnitPrice = 25
  FROM Customer C
  JOIN [Order] O ON O.CustomerId = C.Id
  JOIN OrderItem I ON O.Id = I.OrderId 
  JOIN Product P ON P.Id = I.ProductId
 WHERE C.FirstName = 'Paul' AND C.LastName = 'Henriot'
   AND P.ProductName = 'Queso Cabrales'

This UPDATE performs 3 JOINs with 4 tables. C, O, I, and P are table aliases.

• Our Sql UPDATE Tutorial
• Our Sql DELETE JOIN Tutorial
• Our Sql JOIN 3 Tables Tutorial
• Our Sql JOIN Multiple Tables Tutorial