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));

See our Model Database

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