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
Sample table: agent1
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:
UPDATE agent1
SET commission=commission+.02
WHERE 2>=(
SELECT COUNT(cust_code) FROM customer
WHERE customer.agent_code=agent1.agent_code);
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
Sample table: agent1
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:
UPDATE agent1
SET commission=commission-.02
WHERE agent_code NOT IN(
SELECT agent_code FROM orders a
WHERE ord_amount=(
SELECT ord_amount FROM orders b
WHERE a.ord_date=b.ord_date));
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
Sample table: agent1
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:
UPDATE agent1
SET commission=commission-.02
WHERE agent_code IN(
SELECT agent_code FROM orders a
WHERE ord_amount=(
SELECT MIN(ord_amount) FROM orders b
WHERE a.ord_date=b.ord_date));
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 sum function and group by
Next: SQL Delete
SQL: Tips of the Day
Concatenate strings of a string field in a PostgreSQL 'group by' query:
Input:
ID COMPANY_ID EMPLOYEE 1 1 Anna 2 1 Bill 3 2 Carol 4 2 Dave
SELECT company_id, string_agg(employee, ', ') FROM mytable GROUP BY company_id;
Output:
COMPANY_ID EMPLOYEE 1 Anna, Bill 2 Carol, Dave
Database: PostgreSQL
Ref: https://bit.ly/2XTiRjq
- Weekly Trends
- Python Interview Questions and Answers: Comprehensive Guide
- Scala Exercises, Practice, Solution
- Kotlin Exercises practice with solution
- MongoDB Exercises, Practice, Solution
- SQL Exercises, Practice, Solution - JOINS
- Java Basic Programming Exercises
- SQL Subqueries
- Adventureworks Database Exercises
- C# Sharp Basic Exercises
- SQL COUNT() with distinct
- JavaScript String Exercises
- JavaScript HTML Form Validation
- Java Collection Exercises
- SQL COUNT() function
- SQL Inner Join
We are closing our Disqus commenting system for some maintenanace issues. You may write to us at reach[at]yahoo[dot]com or visit us at Facebook