04. Inserting, Updating, and Deleting Data ----> 08- Using Subqueries in Updates - Exercise

1
  1. Inserting, Updating, and Deleting Data
    08- Using Subqueries in Updates - Exercise
    — Write a SQL statement to
    — Update the comments as gold customers
    — customers who have more than 3000 points.

-------------------------

USE sql_store;
SELECT Customer_ID, POINTS
FROM Customers
WHERE Points > 3000;

UPDATE Orders
SET Comments = ‘Gold customers’
WHERE Customer_ID IN
(SELECT Customer_ID
FROM Customers
WHERE Points > 3000);

-------------------------------------------

USE sql_store;
SELECT Customer_ID
FROM Customers
WHERE Points > 3000;

UPDATE Orders
SET Comments = ‘Gold customers’
WHERE Customer_ID IN (3,5,6);

2

SELECT *
FROM Orders
WHERE Customer_ID in (3,5,6)

Order_ID Customer_ID Order_Date Status Comments Shipped_Date Shipper_ID
5 5 8/25/2017 2 Gold customers 8/26/2017 3
8 5 6/8/2018 1 Gold customers NULL NULL
1 6 1/30/2019 1 Gold customers NULL NULL
10 6 4/22/2018 2 Gold customers 4/23/2018 2

Customer_ID #3 has no order that time!

3

SELECT *
FROM Orders
WHERE Comments = ‘Gold customers’;

Order_ID Customer_ID Order_Date Status Comments Shipped_Date Shipper_ID
1 6 1/30/2019 1 Gold customers NULL NULL
5 5 8/25/2017 2 Gold customers 8/26/2017 3
8 5 6/8/2018 1 Gold customers NULL NULL
10 6 4/22/2018 2 Gold customers 4/23/2018 2

Same result!