Introduction
INSERT with SELECT
Referring to other tables
INSERT, UPDATE and DELETE with JOIN
INSERT, UPDATE, and DELETE with subqueries
Summary
Instruction
Very well done!
As you probably guessed, JOIN is also used in UPDATE queries. Its usage is quite similar to how we used it in the INSERT INTO statement. Look at the query below, which decreases the price of a product if the order amount is more than 1000:
UPDATE Purchase SET Purchase.TotalPrice = Product.Price * 0.75 * Purchase.Quantity FROM Purchase JOIN Product ON Purchase.ProductId = Product.Id WHERE Purchase.TotalPrice > 1000;We have to use the referenced table,
Product, to get the price of the product; that's why we used
JOIN in the
SELECT clause.
Exercise
Decrease the value of ordered products (Purchase table) by 15% if the product's TotalPrice (Product table) exceeds 10.
Stuck? Here's a hint!
Use:
SET Purchase.TotalPrice = Product.Price * 0.85 * Purchase.Quantity
