How To Update Tables With Joins In SQL

In this blog, 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.

Introduction

In this blog, 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 practice, we need to create tables, and account for alterations that may be lead us to update the table's data.

We can use iteration While loop or a Cursor for the same purpose but in this blog, we will be updating tables with joins in SQL.

Let's start.
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

 

Now we have two tables with a common countries id column, we added a column named Countrysortname in the 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 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

Update Command

UPDATE st 
SET    st.countrysortname = ct.sortname
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

Update Command

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

Hope this will help you.

Thanks.