SQL UPDATE JOIN

The SQL UPDATE JOIN statement allows updating a table based on the values in another table. This is commonly used in scenarios where data needs to be synchronized or updated dynamically between related tables. A JOIN is used to match rows between the tables, and the SET clause specifies the updates.

Example

This example updates the category of a product in the Orders table based on data from another table, Categories.

-- Create the Categories table
CREATE TABLE Categories (
    Product VARCHAR(50),
    NewCategory VARCHAR(50)
);

-- Insert sample data into Categories
INSERT INTO Categories (Product, NewCategory)
VALUES ('Phone', 'Mobile Devices');

-- Update the Orders table using a JOIN
UPDATE Orders
SET Orders.Category = Categories.NewCategory
FROM Orders
INNER JOIN Categories
ON Orders.Product = Categories.Product;

-- View the updated table
SELECT * FROM Orders;
            

Explanation

- The Categories table contains the updated category for the product "Phone".
- The UPDATE JOIN matches rows between Orders and Categories using the product name.
- The SET clause updates the Category column in the Orders table.
- The output displays the updated category for "Phone" as "Mobile Devices".