June 30, 2021 08:53 by
PeterIn 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)
SQL
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.