How To Update a Table Neat Way Using Subquery

Here’s a neat way to update a Table using SQL Subquery. Subqueries’ the real purpose is you can use more than one table. Simple SQL query fetches records from one table and the same for Update too. In the case of SQL Subquery, you can use more than one table in Update.

How to write a SQL subquery with two tables, I will present here. Frankly, you can say the ability to write a subquery is an advanced skill. Don’t worry, here I have simplified it for you.

DB2: Subquery to Update a Table

I use here two tables EMP1 and EMP2. I will update EMP1 Table when I get the matching row from EMP2 Table. I would say the benefit is you can use Two Tables.

SQL Query

Subquery used in WHERE clause of main query.

UPDATE EMP1 
SET SAL = 100000
WHERE EMP_NO IN
(SELECT EMP_NO
FROM EMP2
WHERE DEPT_NO IS NULL);
===> Subquery

EMP1 Table

EMP_NOSAL
100190000
100285000

EMP2 Table

EMP_NODEPT_ NO
1001IT
1002(NULL)

Explanation

The Subquery fetches employee number for who dept_no is NULL from EMP2 Table. Based on this, you can easily update the EMP1 Table with new salary. This is useful query for DB2 developers who are working in Retail projects.

Related Posts

Latest from the Blog

Exclusive Loops Vs Iterator Object in Python

Loops and iterators are two unique concepts. But the outcome is the same. Here are details explained with simple examples.

Author: Srini

Experienced software developer. Skills in Development, Coding, Testing and Debugging. Good Data analytic skills (Data Warehousing and BI). Also skills in Mainframe.