SQL UPDATE with JOIN

SQL UPDATE JOIN Explained

A SQL UPDATE statement can include JOIN operations.

It can contain zero, one, or multiple JOIN operations.

The UPDATE affects records that satisfy the JOIN conditions.

Example

#

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

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

Syntax

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.
INNER JOIN is the most commonly used type of JOIN operation.


More Examples

SQL UPDATE with LEFT JOIN

PRODUCT
Id
ProductName
SupplierId
UnitPrice
Package
IsDiscontinued
ORDERITEM
Id
OrderId
ProductId
UnitPrice
Quantity
Problem: Discontinue products that have not sold.
UPDATE Product
   SET IsDiscontinued = 1
  FROM Product
  LEFT JOIN OrderItem ON Product.Id = OrderItem.ProductId
 WHERE OrderItem.Id IS NULL

Note: this UPDATE statement uses a LEFT JOIN operation.

Result:  1 record updated.

SQL UPDATE with 3 JOINs

Problem: For customer Paul Henriot change the unit price to $25 for Queso Calibres in their order.
PRODUCT
Id
ProductName
SupplierId
UnitPrice
Package
IsDiscontinued
ORDERITEM
Id
OrderId
ProductId
UnitPrice
Quantity
ORDER
Id
OrderDate
OrderNumber
CustomerId
TotalAmount

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 a 4-table JOIN.
The C, O, I, and P are table Aliases.

Result:  1 record updated

You may also like



Guides