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.
Practice SQL Exercises
- SQL Exercises, Practice, Solution
- SQL Retrieve data from tables [33 Exercises]
- SQL Boolean and Relational operators [12 Exercises]
- SQL Wildcard and Special operators [22 Exercises]
- SQL Aggregate Functions [25 Exercises]
- SQL Formatting query output [10 Exercises]
- SQL Quering on Multiple Tables [8 Exercises]
- FILTERING and SORTING on HR Database [38 Exercises]
- SQL JOINS
- SQL SUBQUERIES
- SQL Union[9 Exercises]
- SQL View[16 Exercises]
- SQL User Account Management [16 Exercise]
- Movie Database
- BASIC queries on movie Database [10 Exercises]
- SUBQUERIES on movie Database [16 Exercises]
- JOINS on movie Database [24 Exercises]
- Soccer Database
- Introduction
- BASIC queries on soccer Database [29 Exercises]
- SUBQUERIES on soccer Database [33 Exercises]
- Hospital Database
- Employee Database
- More to come!
Want to improve the above article? Contribute your Notes/Comments/Examples through Disqus.
Previous: Update columns using sum function and group by
Next: SQL Delete
SQL: Tips of the Day
SQL Server SELECT into existing table.
INSERT INTO dbo.TABLETWO SELECT col1, col2 FROM dbo.TABLEONE WHERE col3 LIKE @search_key
This assumes there's only two columns in dbo.TABLETWO - you need to specify the columns otherwise:
INSERT INTO dbo.TABLETWO (col1, col2) SELECT col1, col2 FROM dbo.TABLEONE WHERE col3 LIKE @search_key
Database: SQL Server
Ref: https://bit.ly/3y6tpA3
- New Content published on w3resource:
- HTML-CSS Practical: Exercises, Practice, Solution
- Java Regular Expression: Exercises, Practice, Solution
- Scala Programming Exercises, Practice, Solution
- Python Itertools exercises
- Python Numpy exercises
- Python GeoPy Package exercises
- Python Pandas exercises
- Python nltk exercises
- Python BeautifulSoup exercises
- Form Template
- Composer - PHP Package Manager
- PHPUnit - PHP Testing
- Laravel - PHP Framework
- Angular - JavaScript Framework
- Vue - JavaScript Framework
- Jest - JavaScript Testing Framework