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: 

                               

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