SQL update using subqueries
In this page, we are discussing the usage of a subquery to update the values of columns with the UPDATE statement.
Example:
Sample table : customer
+-----------+-------------+-------------+--------------+--------------+-------+-------------+-------------+-------------+---------------+--------------+------------+ |CUST_CODE | CUST_NAME | CUST_CITY | WORKING_AREA | CUST_COUNTRY | GRADE | OPENING_AMT | RECEIVE_AMT | PAYMENT_AMT |OUTSTANDING_AMT| PHONE_NO | AGENT_CODE | +-----------+-------------+-------------+--------------+--------------+-------+-------------+-------------+-------------+---------------+--------------+------------+ | C00013 | Holmes | London | London | UK | 2 | 6000.00 | 5000.00 | 7000.00 | 4000.00 | BBBBBBB | A003 | | C00001 | Micheal | New York | New York | USA | 2 | 3000.00 | 5000.00 | 2000.00 | 6000.00 | CCCCCCC | A008 | | C00020 | Albert | New York | New York | USA | 3 | 5000.00 | 7000.00 | 6000.00 | 6000.00 | BBBBSBB | A008 | | C00025 | Ravindran | Bangalore | Bangalore | India | 2 | 5000.00 | 7000.00 | 4000.00 | 8000.00 | AVAVAVA | A011 | | C00024 | Cook | London | London | UK | 2 | 4000.00 | 9000.00 | 7000.00 | 6000.00 | FSDDSDF | A006 | | C00015 | Stuart | London | London | UK | 1 | 6000.00 | 8000.00 | 3000.00 | 11000.00 | GFSGERS | A003 | | C00002 | Bolt | New York | New York | USA | 3 | 5000.00 | 7000.00 | 9000.00 | 3000.00 | DDNRDRH | A008 | | C00018 | Fleming | Brisban | Brisban | Australia | 2 | 7000.00 | 7000.00 | 9000.00 | 5000.00 | NHBGVFC | A005 | | C00021 | Jacks | Brisban | Brisban | Australia | 1 | 7000.00 | 7000.00 | 7000.00 | 7000.00 | WERTGDF | A005 | ........ | C00011 | Sundariya | Chennai | Chennai | India | 3 | 7000.00 | 11000.00 | 7000.00 | 11000.00 | PPHGRTS | A010 | +-----------+-------------+-------------+--------------+--------------+-------+-------------+-------------+-------------+---------------+--------------+------------+
Sample table: agents
+------------+----------------------+--------------------+------------+-----------------+---------+ | AGENT_CODE | AGENT_NAME | WORKING_AREA | COMMISSION | PHONE_NO | COUNTRY | +------------+----------------------+--------------------+------------+-----------------+---------+ | A007 | Ramasundar | Bangalore | 0.15 | 077-25814763 | | | A003 | Alex | London | 0.13 | 075-12458969 | | | A008 | Alford | New York | 0.12 | 044-25874365 | | | A011 | Ravi Kumar | Bangalore | 0.15 | 077-45625874 | | | A010 | Santakumar | Chennai | 0.14 | 007-22388644 | | | A012 | Lucida | San Jose | 0.12 | 044-52981425 | | | A005 | Anderson | Brisban | 0.13 | 045-21447739 | | | A001 | Subbarao | Bangalore | 0.14 | 077-12346674 | | | A002 | Mukesh | Mumbai | 0.11 | 029-12358964 | | ........ | A009 | Benjamin | Hampshair | 0.11 | 008-22536178 | | +------------+----------------------+--------------------+------------+-----------------+---------+
To update the 'agent1' table with following conditions -
1. modified value for 'commission' is 'commission'+.02,
2. the number 2 is greater than or equal to the number of 'cust_code' from 'customer' table which satisfies the condition bellow :
3. 'agent_code' of 'customer' table and 'agent1' table should match,
the following SQL statement can be used:
SQL Code:
-- This SQL code updates the 'commission' column in the 'agent1' table by adding 0.02 to the existing commission for agents who have fewer than or equal to 2 associated customers.
-- UPDATE statement begins
UPDATE agent1
-- Specifies the target table 'agent1' where the data will be updated
SET commission=commission+.02
-- Increases the value of the 'commission' column by 0.02 for rows that meet the specified condition
WHERE 2>=(
-- Subquery counts the number of customers associated with each agent
SELECT COUNT(cust_code) FROM customer
-- Matches customers in the 'customer' table with their agents in the 'agent1' table based on 'agent_code'
WHERE customer.agent_code=agent1.agent_code
);
-- Specifies the condition for updating rows: only rows where the count of associated customers is less than or equal to 2 will be affected
Explanation:
- This SQL code uses the UPDATE statement to modify existing records in the 'agent1' table.
- The SET clause increases the value of the 'commission' column by 0.02 for agents who have fewer than or equal to 2 associated customers.
- The WHERE clause includes a subquery that counts the number of customers associated with each agent.
- The subquery result is compared to 2 in the outer WHERE clause to filter rows, ensuring that only agents with two or fewer associated customers will have their commission increased.
Output:
SQL update using subqueries with 'IN'
In the following we are going to discuss the usage of IN within a subquery with the UPDATE statement, to update the specified columns.
Example:
Sample table: orders
ORD_NUM ORD_AMOUNT ADVANCE_AMOUNT ORD_DATE CUST_CODE AGENT_CODE ORD_DESCRIPTION
---------- ---------- -------------- --------- --------------- --------------- -----------------
200114 3500 2000 15-AUG-08 C00002 A008
200122 2500 400 16-SEP-08 C00003 A004
200118 500 100 20-JUL-08 C00023 A006
200119 4000 700 16-SEP-08 C00007 A010
200121 1500 600 23-SEP-08 C00008 A004
200130 2500 400 30-JUL-08 C00025 A011
200134 4200 1800 25-SEP-08 C00004 A005
200108 4000 600 15-FEB-08 C00008 A004
200103 1500 700 15-MAY-08 C00021 A005
200105 2500 500 18-JUL-08 C00025 A011
..........
200102 2000 300 25-MAY-08 C00012 A012
Sample table: agents1
+------------+----------------------+--------------------+------------+-----------------+---------+ | AGENT_CODE | AGENT_NAME | WORKING_AREA | COMMISSION | PHONE_NO | COUNTRY | +------------+----------------------+--------------------+------------+-----------------+---------+ | A007 | Ramasundar | Bangalore | 0.15 | 077-25814763 | | | A003 | Alex | London | 0.13 | 075-12458969 | | | A008 | Alford | New York | 0.12 | 044-25874365 | | | A011 | Ravi Kumar | Bangalore | 0.15 | 077-45625874 | | | A010 | Santakumar | Chennai | 0.14 | 007-22388644 | | | A012 | Lucida | San Jose | 0.12 | 044-52981425 | | | A005 | Anderson | Brisban | 0.13 | 045-21447739 | | | A001 | Subbarao | Bangalore | 0.14 | 077-12346674 | | | A002 | Mukesh | Mumbai | 0.11 | 029-12358964 | | ...... | A009 | Benjamin | Hampshair | 0.11 | 008-22536178 | | +------------+----------------------+--------------------+------------+-----------------+---------+
To update the 'agent1' table with following conditions -
1. modified value for 'commission' is 'commission'-.02,
2. 'agent_code' not within the selected 'agent_code' of 'orders' table named as alias 'a' which satisfies the condition bellow :
3. 'ord_amount' of 'orders' table named as alias 'a' is equal to the 'ord_amount' of 'orders' table named as alias 'b' which satisfies the condition bellow :
4.'ord_date' of alias 'a'and'b'must be same,
the following SQL statement can be used:
SQL Code:
-- This SQL code updates the 'commission' column in the 'agent1' table by subtracting 0.02 from the existing commission for agents whose order amount on a certain date does not match any other order amount on the same date.
-- UPDATE statement begins
UPDATE agent1
-- Specifies the target table 'agent1' where the data will be updated
SET commission=commission-.02
-- Decreases the value of the 'commission' column by 0.02 for rows that meet the specified condition
WHERE agent_code NOT IN(
-- Subquery retrieves the 'agent_code' associated with orders whose 'ord_amount' matches any other order's 'ord_amount' on the same date
SELECT agent_code FROM orders a
-- First subquery level retrieves the 'ord_amount' for each order on a specific date
WHERE ord_amount=(
-- Second subquery level retrieves the 'ord_amount' for each order on the same date as the outer query
SELECT ord_amount FROM orders b
WHERE a.ord_date=b.ord_date
)
);
-- Specifies the condition for updating rows: only rows where the 'agent_code' does not match any other order's 'agent_code' with the same 'ord_amount' on the same date will be affected
Explanation:
- This SQL code uses the UPDATE statement to modify existing records in the 'agent1' table.
- The SET clause decreases the value of the 'commission' column by 0.02 for agents whose order amount on a certain date does not match any other order amount on the same date.
- The WHERE clause includes a subquery that checks whether an agent's 'agent_code' is not associated with any other order's 'agent_code' with the same 'ord_amount' on the same date.
- If the condition is met, the commission is decreased by 0.02 for that agent. Otherwise, no update is performed for that agent.
SQL update using subqueries with 'IN' and min()
In the following we are going to discuss the usage of IN operator and MIN() function along with the UPDATE statement to make changes within the specified columns.
Example:
Sample table: orders
ORD_NUM ORD_AMOUNT ADVANCE_AMOUNT ORD_DATE CUST_CODE AGENT_CODE ORD_DESCRIPTION
---------- ---------- -------------- --------- --------------- --------------- -----------------
200114 3500 2000 15-AUG-08 C00002 A008
200122 2500 400 16-SEP-08 C00003 A004
200118 500 100 20-JUL-08 C00023 A006
200119 4000 700 16-SEP-08 C00007 A010
200121 1500 600 23-SEP-08 C00008 A004
200130 2500 400 30-JUL-08 C00025 A011
200134 4200 1800 25-SEP-08 C00004 A005
200108 4000 600 15-FEB-08 C00008 A004
200103 1500 700 15-MAY-08 C00021 A005
200105 2500 500 18-JUL-08 C00025 A011
..........
200102 2000 300 25-MAY-08 C00012 A012
Sample table: agents1
+------------+----------------------+--------------------+------------+-----------------+---------+ | AGENT_CODE | AGENT_NAME | WORKING_AREA | COMMISSION | PHONE_NO | COUNTRY | +------------+----------------------+--------------------+------------+-----------------+---------+ | A007 | Ramasundar | Bangalore | 0.15 | 077-25814763 | | | A003 | Alex | London | 0.13 | 075-12458969 | | | A008 | Alford | New York | 0.12 | 044-25874365 | | | A011 | Ravi Kumar | Bangalore | 0.15 | 077-45625874 | | | A010 | Santakumar | Chennai | 0.14 | 007-22388644 | | | A012 | Lucida | San Jose | 0.12 | 044-52981425 | | | A005 | Anderson | Brisban | 0.13 | 045-21447739 | | | A001 | Subbarao | Bangalore | 0.14 | 077-12346674 | | | A002 | Mukesh | Mumbai | 0.11 | 029-12358964 | | ....... | A009 | Benjamin | Hampshair | 0.11 | 008-22536178 | | +------------+----------------------+--------------------+------------+-----------------+---------+
To update the 'agent1' table with following conditions -
1. modified value for 'commission' is 'commission'-.02,
2. 'agent_code' not within the selected 'agent_code' of 'orders' table named as alias 'a' which satisfies the condition bellow :
3. 'ord_amount' of 'orders' table named as alias 'a' is equal to the minimum 'ord_amount' of 'orders' table named as alias 'b' which satisfies the condition bellow :
4. 'ord_date' of alias 'a' and 'b' must be same,
the following SQL statement can be used:
SQL Code:
-- This SQL code updates the 'commission' column in the 'agent1' table by subtracting 0.02 from the existing commission for agents whose order amount on a certain date is the minimum order amount for that date.
-- UPDATE statement begins
UPDATE agent1
-- Specifies the target table 'agent1' where the data will be updated
SET commission=commission-.02
-- Decreases the value of the 'commission' column by 0.02 for rows that meet the specified condition
WHERE agent_code IN(
-- Subquery retrieves the 'agent_code' associated with orders whose 'ord_amount' matches the minimum order amount on the same date
SELECT agent_code FROM orders a
-- First subquery level retrieves the 'ord_amount' for each order on a specific date
WHERE ord_amount=(
-- Second subquery level retrieves the minimum 'ord_amount' for each date
SELECT MIN(ord_amount) FROM orders b
WHERE a.ord_date=b.ord_date
)
);
-- Specifies the condition for updating rows: only rows where the 'agent_code' is associated with the minimum order amount on the same date will be affected
Explanation:
- This SQL code uses the UPDATE statement to modify existing records in the 'agent1' table.
- The SET clause decreases the value of the 'commission' column by 0.02 for agents whose order amount on a certain date matches the minimum order amount for that date.
- The WHERE clause includes a subquery that retrieves the 'agent_code' associated with orders whose 'ord_amount' matches the minimum order amount on the same date.
- If the condition is met, the commission is decreased by 0.02 for those agents. Otherwise, no update is performed for those agents.
Here is a new document which is a collection of questions with short and simple answers, useful for learning SQL as well as for interviews.
Check out our 1000+ SQL Exercises with solution and explanation to improve your skills.
PREV : Update columns using sum function and group by
NEXT : SQL Delete