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)
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:
just replace the select command to update with where clause comparing with l.id and r.id
[Download SQL Scripts via Google Drive]
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
ALTER TABLE STATES ADD countrysortname NVARCHAR(5)
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.
1 comments
Hi ,
ReplyDeleteCan you please some sample of query to use DML operation in table using View in sql.
Thanks in Advance