SQL update columns with sum() and group by
In this page, we are going to discuss, how to change the data of the columns with the SQL UPDATE statement using aggregate function SUM() and GROUP BY clause.
Example:
Sample table : customer1+-----------+-------------+-------------+--------------+--------------+-------+-------------+-------------+-------------+---------------+--------------+------------+ |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 | | C00019 | Yearannaidu | Chennai | Chennai | India | 1 | 8000.00 | 7000.00 | 7000.00 | 8000.00 | ZZZZBFV | A010 | | C00005 | Sasikant | Mumbai | Mumbai | India | 1 | 7000.00 | 11000.00 | 7000.00 | 11000.00 | 147-25896312 | A002 | | C00007 | Ramanathan | Chennai | Chennai | India | 1 | 7000.00 | 11000.00 | 9000.00 | 9000.00 | GHRDWSD | A010 | | C00022 | Avinash | Mumbai | Mumbai | India | 2 | 7000.00 | 11000.00 | 9000.00 | 9000.00 | 113-12345678 | A002 | | C00004 | Winston | Brisban | Brisban | Australia | 1 | 5000.00 | 8000.00 | 7000.00 | 6000.00 | AAAAAAA | A005 | | C00023 | Karl | London | London | UK | 0 | 4000.00 | 6000.00 | 7000.00 | 3000.00 | AAAABAA | A006 | | C00006 | Shilton | Torento | Torento | Canada | 1 | 10000.00 | 7000.00 | 6000.00 | 11000.00 | DDDDDDD | A004 | | C00010 | Charles | Hampshair | Hampshair | UK | 3 | 6000.00 | 4000.00 | 5000.00 | 5000.00 | MMMMMMM | A009 | | C00017 | Srinivas | Bangalore | Bangalore | India | 2 | 8000.00 | 4000.00 | 3000.00 | 9000.00 | AAAAAAB | A007 | | C00012 | Steven | San Jose | San Jose | USA | 1 | 5000.00 | 7000.00 | 9000.00 | 3000.00 | KRFYGJK | A012 | | C00008 | Karolina | Torento | Torento | Canada | 1 | 7000.00 | 7000.00 | 9000.00 | 5000.00 | HJKORED | A004 | | C00003 | Martin | Torento | Torento | Canada | 2 | 8000.00 | 7000.00 | 7000.00 | 8000.00 | MJYURFD | A004 | | C00009 | Ramesh | Mumbai | Mumbai | India | 3 | 8000.00 | 7000.00 | 3000.00 | 12000.00 | Phone No | A002 | | C00014 | Rangarappa | Bangalore | Bangalore | India | 2 | 8000.00 | 11000.00 | 7000.00 | 12000.00 | AAAATGF | A001 | | C00016 | Venkatpati | Bangalore | Bangalore | India | 2 | 8000.00 | 11000.00 | 7000.00 | 12000.00 | JRTVFDD | A007 | | C00011 | Sundariya | Chennai | Chennai | India | 3 | 7000.00 | 11000.00 | 7000.00 | 11000.00 | PPHGRTS | A010 | +-----------+-------------+-------------+--------------+--------------+-------+-------------+-------------+-------------+---------------+--------------+------------+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
200109 3500 800 30-JUL-08 C00011 A010
200101 3000 1000 15-JUL-08 C00001 A008
200111 1000 300 10-JUL-08 C00020 A008
200104 1500 500 13-MAR-08 C00006 A004
200106 2500 700 20-APR-08 C00005 A002
200125 2000 600 10-OCT-08 C00018 A005
200117 800 200 20-OCT-08 C00014 A001
200123 500 100 16-SEP-08 C00022 A002
200120 500 100 20-JUL-08 C00009 A002
200116 500 100 13-JUL-08 C00010 A009
200124 500 100 20-JUN-08 C00017 A007
200126 500 100 24-JUN-08 C00022 A002
200129 2500 500 20-JUL-08 C00024 A006
200127 2500 400 20-JUL-08 C00015 A003
200128 3500 1500 20-JUL-08 C00009 A002
200135 2000 800 16-SEP-08 C00007 A010
200131 900 150 26-AUG-08 C00012 A012
200133 1200 400 29-JUN-08 C00009 A002
200100 1000 600 08-JAN-08 C00015 A003
200110 3000 500 15-APR-08 C00019 A010
200107 4500 900 30-AUG-08 C00007 A010
200112 2000 400 30-MAY-08 C00016 A007
200113 4000 600 10-JUN-08 C00022 A002
200102 2000 300 25-MAY-08 C00012 A012
To change the value of 'outstanding_amt' of 'customer1' table with following conditions -
1. modified value for 'outstanding_amt' is 0,
2. sum of 'ord_amount' from 'orders' table must be greater than 5000 which satisfies the condition bellow:
3. unique 'cust_code' of 'customer1' makes a group,
4. and 'cust_code' of 'customer1' and 'orders' must be same,
the following SQL statement can be used:
SQL Code:
-- This SQL code updates the 'outstanding_amt' column in the 'customer1' table to 0 for customers whose total order amount exceeds 5000.
-- UPDATE statement begins
UPDATE customer1
-- Specifies the target table 'customer1' where the data will be updated
SET outstanding_amt=0
-- Sets the value of the 'outstanding_amt' column to 0 for rows that meet the specified condition
WHERE (SELECT SUM(ord_amount) FROM orders
-- Subquery calculates the total order amount for each customer
WHERE customer1.cust_code=orders.cust_code
-- Matches customers in 'customer1' with their orders in 'orders' based on 'cust_code'
GROUP BY cust_code )>5000;
-- Specifies the condition for updating rows: only rows where the total order amount for a customer exceeds 5000 will be affected
Explanation:
- This SQL code uses the UPDATE statement to modify existing records in the 'customer1' table.
- The UPDATE statement specifies the target table 'customer1' where the update operation will be performed.
- The SET clause assigns a new value of 0 to the 'outstanding_amt' column for rows that meet the specified condition.
- The WHERE clause includes a subquery that calculates the total order amount for each customer in the 'customer1' table by joining it with the 'orders' table using the 'cust_code' column.
- The subquery result is compared to 5000 in the outer WHERE clause to filter rows, ensuring that only customers with a total order amount exceeding 5000 will have their 'outstanding_amt' set to 0.
SQL update columns with NULL
In the following we are going to discuss, how the NULL works with the UPDATE statement.
Example:
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 | | | A006 | McDen | London | 0.15 | 078-22255588 | | | A004 | Ivan | Torento | 0.15 | 008-22544166 | | | A009 | Benjamin | Hampshair | 0.11 | 008-22536178 | | +------------+----------------------+--------------------+------------+-----------------+---------+
To update the 'agent1' table with following conditions -
1. modified value for 'phone_no' is NULL,
2. 'commission' must be more than .15,
the following SQL statement can be used :
SQL Code:
-- This SQL code updates the 'phone_no' column in the 'agent1' table to NULL for agents whose commission is greater than or equal to 0.15.
-- UPDATE statement begins
UPDATE agent1
-- Specifies the target table 'agent1' where the data will be updated
SET phone_no=NULL
-- Sets the value of the 'phone_no' column to NULL for rows that meet the specified condition
WHERE commission>=.15;
-- Specifies the condition for updating rows: only rows where the 'commission' column is greater than or equal to 0.15 will be affected
Explanation:
- This SQL code uses the UPDATE statement to modify existing records in the 'agent1' table.
- The UPDATE statement specifies the target table 'agent1' where the update operation will be performed.
- The SET clause assigns a new value of NULL to the 'phone_no' column for rows that meet the specified condition.
- The WHERE clause filters the rows to be updated, ensuring that only rows where the 'commission' column is greater than or equal to 0.15 will be affected by the update operation.
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.
Previous: Update columns using arithmetical expression
Next: UPDATE using subqueries