TSQL Example: UPDATE Statement in Combination with JOIN and WHERE Clauses

In this post, we will walk through a real-world example of using UPDATE statement that involves a JOIN and a WHERE. Let's assume you have two tables: Orders and Products and you want to update the prices of products based on certain conditions related to orders.

Let's assume the tables are structured as follows:

Sample Data for UPDATE

You can use this Fiddle to follow along and practice UPDATE statement with JOIN and WHERE clauses

Create Products and Orders tables with sample data

-- Create Products Table
CREATE TABLE Products (
    ProductID INT PRIMARY KEY,
    ProductName VARCHAR(50),
    Color VARCHAR(50),
    Price DECIMAL(10, 2)
);

-- Insert data into Products Table
INSERT INTO Products (ProductID, ProductName, Color, Price)
VALUES
    (101, 'Product A', 'Blue', 10.00),
    (102, 'Product B', 'Red', 15.00),
    (103, 'Product C', 'White', 20.00);

-- Create Orders Table
CREATE TABLE Orders (
    OrderID INT PRIMARY KEY,
    ProductID INT,
    Quantity INT,
    FOREIGN KEY (ProductID) REFERENCES Products(ProductID)
);

-- Insert data into Orders Table
INSERT INTO Orders (OrderID, ProductID, Quantity)
VALUES
    (1, 101, 51),
    (2, 102, 50),
    (3, 103, 2);

SELECT *
FROM Products;

SELECT *
FROM Orders;

Products Table:

ProductID	ProductName	Color	Price
101	Product A	Blue	10.00
102	Product B	Red	15.00
103	Product C	White	20.00

Orders Table:

OrderID	ProductID	Quantity
1	101	51
2	102	50
3	103	2

UPDATE Operation with JOIN and WHERE Clauses

You want to increase the prices of products by 4% if total quantity ordered is greater than 5 and the product's color is Blue. Here's how you can do this with a complex UPDATE statement involving a JOIN and WHERE:

UPDATE Products
SET Price = Price * 1.04 -- Increase price by 4%
FROM Products
INNER JOIN (
	SELECT ProductID
	FROM Orders
	GROUP BY ProductID
	HAVING SUM(Quantity) > 5
	) AS HighDemandProducts ON Products.ProductID = HighDemandProducts.ProductID
WHERE Products.Color = 'Blue';

The above SQL UPDATE statement is used to update the prices of certain products in the "Products" table. Let's break down the statement step by step:

1. UPDATE Products: This initiates an UPDATE operation on the "Products" table, indicating that we want to modify data in this table.

2. SET Price = Price * 1.04: This line updates the "Price" column of the "Products" table. It multiplies the current price by 1.04, effectively increasing it by 4%. This is done for the products that meet the conditions specified in the subsequent clauses.

3. FROM Products: The FROM clause specifies the source of data for the update operation. In this case, it's the same "Products" table that we are updating.

4. INNER JOIN: This is used to combine data from different tables based on a common column, in this case, the "ProductID" column. The INNER JOIN is used to join the "Products" table with a subquery derived from the "Orders" table.

5. ( SELECT ProductID FROM Orders GROUP BY ProductID HAVING SUM(Quantity) > 5 ) AS HighDemandProducts: This subquery retrieves the "ProductID" values from the "Orders" table for products that have a total quantity ordered greater than 5. It groups the data by product and applies the HAVING clause to filter products that meet the demand criteria.

6. ON Products.ProductID = HighDemandProducts.ProductID: This part of the JOIN condition specifies that the data should be joined based on matching "ProductID" values between the "Products" table and the "HighDemandProducts" subquery.

7. WHERE Products.Color = 'Blue': This WHERE clause further filters the rows in the "Products" table. Only products with the color "Blue" are considered for the update operation.

In summary, this SQL statement updates the prices of products in the "Products" table, increasing them by 4%, but only for products that are categorized as "High Demand" (those with a total quantity ordered greater than 5) and have the color "Blue". The UPDATE operation is performed using data from the "Products" table and the subquery derived from the "Orders" table, allowing for specific criteria to be met before modifying the prices.

After running this UPDATE statement, the Products table will be updated as follows:

Updated Products Table:

ProductID	ProductName	Color	Price
101	Product A	Blue	10.40
102	Product B	Red	15.00
103	Product C	White	20.00

tip

Notice that the price of Product A has been increased by 4 precent from 10.00 to 10.40.