How to update tables with joins in SQL

In this article, we will see How to update tables with joins in SQL.
When we are dealing with the data we need to store that data in the database like MySQL, Oracle etc, In daily practices, we need to create tables, alterations  that may be lead us to update table's data.

We can use iteration  While loop or a Cursor for the same purpose but today we are talking about updating tables with joins in SQL.



Let's Starts:

First of all, we need a database (example: TestingDatabase) with two tables (example: Countries and second one States) schema as shown below:

Table: Countries

Table: States

We have two tables with common countries id column,  we added a column named Countrysortname in states table using an alter command

             ALTER TABLE STATES ADD countrysortname NVARCHAR(5)

 we can apply inner join on country_id.

Table schema after adding a column 
Table: States

Now, we are ready to update the table States using INNER JOIN.
We need to write a select command first to verify that what we are going to update as shown below: 

Syntax : 
        

SELECT [L.column_name],                [R.column_name] FROM   table_name1 L        JOIN table_name2 R          ON L.column_name = R.column_name UPDATE L SET    [L.Column_name] = [R.Column_name] FROM   table_name1 L        JOIN table_name2 R                     ON L.column_name = R.column_name

                               
Example :

SELECT ct.id,ct.name,ct.sortname,st.id,  st.countrysortname FROM   states st INNER JOIN countries ct ON ct.id = st.country_id  With Subquery:  SELECT l.id,        r.id,        l.countrysortname,        r.sortname        FROM   states l        INNER JOIN (SELECT st.id,                           ct.sortname                    FROM   states st                           INNER JOIN countries ct                                   ON ct.id = st.country_id)r ON l.id = r.id

just replace the select command to update with where clause comparing with l.id and r.id 

UPDATE st  SET    st.countrysortname = ct.sortname FROM   states st INNER JOIN countries ct ON ct.id = st.country_id  Using Subquery: UPDATE l SET    l.countrysortname = r.sortname FROM   states l        INNER JOIN (SELECT st.id,                           ct.sortname                    FROM   states st                           INNER JOIN countries ct                                   ON ct.id = st.country_id)r                ON l.id = r.id WHERE  l.id = r.id

Learn in 10 minutes How To create Database and table using SQL Server with DML Commands  

for practice, you can download the scripts via google drive.

[Download SQL Scripts via Google Drive]