w3resource logo

:

SQL Tutorial

SQL update using subqueries

Description

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 select re-ordering columns

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.