SQL – UPDATE Statement part-2

update2

admin DML Statements, Oracle , ,

Hi Geeks,
In previous blog on UPDATE statement, we learnt, how can we update a column value with a string value or based on any column from the same table and update using DEFAULT keyword?
In this blog we will learn something advance. How can we update a table column with a value which is available in a different table?

Syntax:
Syntax is pretty much same but with a little tweek. In SQL, we call it Correlated Subquery.

UPDATE table_name1 TABLE_ALIAS
SET
column1 = (SELECT column1 FROM table_name2 TABLE_ALIAS WHERE <join condition>),
column2 = (SELECT column2 FROM table_name2 TABLE_ALIAS WHERE <join condition>)
WHERE condition;

You can visit my previous blog if you are not aware of any keywords in the above syntax.
I can understand this syntax can give you a headache but trust me it would look super easy after the example and some self written codes. We will be using the same table we used in the previous lesson.

For better understand, we need one more table with the same date but SALARY as null/blank. So, I have prepared a new table called TEST_UPD_NEW.
Click Here to download the CREATE TABLE script.

TABLE – TEST_UPD
FIRST_NAMELAST_NAMESALARY
StevenKing24000
NeenaKochhar17000
LexDe Haan17000
AlexanderHunold9000
BruceErnst6000
TABLE – TEST_UPD_NEW
FIRST_NAMELAST_NAMESALARY
StevenKing
NeenaKochhar
AlexanderHunold
BruceErnst

You must have understood, what am I going to do next?
Yes, you guessed it RIGHT.
We will try to update SALARY column of our new table on the basis of our old table. Let’s write an SQL statement with the help of syntax above.

Example: 1

Note:
We have not used any WHERE clause for our UPDATE statement. Because, we want to update salary for all the employees. The WHERE clause we have used, it is only for SELECT statement where it is helping to fetch correct SALARY for the respective EMPLOYEE.

Execute above SQL code and we will see that 4 rows updated successfully.

TABLE – TEST_UPD
FIRST_NAMELAST_NAMESALARY
StevenKing24000
NeenaKochhar17000
LexDe Haan17000
AlexanderHunold9000
BruceErnst6000
TABLE – TEST_UPD_NEW
FIRST_NAMELAST_NAMESALARY
StevenKing24000
NeenaKochhar17000
AlexanderHunold9000
BruceErnst6000

So, we have updated salary column in table TEST_UPD_NEW on the basis of salary column from TEST_UPD table.

I have a very interesting task for you.
Write a SQL statement to update salary of Bruce Ernst in table TEST_UPD_NEW with twice of salary of Lex De Haan in TEST_UPD table.
Put your SQL statement in the comment section.

If you have any questions/suggestions, please comment below or write me an email.

If you like this blog, do share with your friends and colleagues on your social media.
For more updates join my facebook group and do like my facebook page.

Thank you,
Kapil Kumar

Sharing is caring!

You May Also Like..

WHERE

SQL – WHERE clause

Hi DBgeek Army, Welcome back to the world of Oracle! Where clause helps to select desired rows or helps to […]

comments

Comment in SQL and PL-SQL

Hi DBgeek Army, Welcome back to the world of Oracle! Comments are very important aspects of our code. No matter […]

merge

SQL – MERGE Statement

Hi Devs, As we have already learned INSERT INTO and UPDATE statement, our next very important statement is MERGE. Merge […]

Leave a Reply

Your email address will not be published. Required fields are marked *