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.



How to update tables with joins in SQL

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



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



[Download SQL Scripts via Google Drive] 

Protected by Copyscape

Post a Comment

1 Comments

  1. Hi ,
    Can you please some sample of query to use DML operation in table using View in sql.


    Thanks in Advance

    ReplyDelete