DB GEEK

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.

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.

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

UPDATE test_upd_new a
SET
    a.salary = (
        SELECT
            salary FROM test_upd b
        WHERE
            a.first_name = b.first_name
            AND a.last_name = b.last_name
    );
    

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.

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!