SQL – UPDATE Statement part-2

update2

admin 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_NAME LAST_NAME SALARY
Steven King 24000
Neena Kochhar 17000
Lex De Haan 17000
Alexander Hunold 9000
Bruce Ernst 6000
TABLE – TEST_UPD_NEW
FIRST_NAME LAST_NAME SALARY
Steven King
Neena Kochhar
Alexander Hunold
Bruce Ernst

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_NAME LAST_NAME SALARY
Steven King 24000
Neena Kochhar 17000
Lex De Haan 17000
Alexander Hunold 9000
Bruce Ernst 6000
TABLE – TEST_UPD_NEW
FIRST_NAME LAST_NAME SALARY
Steven King 24000
Neena Kochhar 17000
Alexander Hunold 9000
Bruce Ernst 6000

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..

merge

SQL – MERGE Statement

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

update

SQL – UPDATE Statement part-1

Hi Geeks, We already have a separate blog on INSERT INTO statement. So today, we will talk about UPDATE statement […]

Translate

Oracle – Translate Function

Hi Geeks, Today, we will talk about an Oracle’s inbuilt function TRANSLATE. TRANSLATE works same as REPLACE function does but […]

Leave a Reply

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