Let’s discuss the SQL update statement when using joins in SQL. Many of us try to make this complicated when we try to use update statement when dealing with two tables using joins. Please follow along for this short SQL lesson on UPDATE USING JOINS.
Suppose we have 2 tables that you want to use for getting the data and then want to update a particular column. We will first create a sample database and then create 2 tables and then update the tables using Joins.
Step 1: Creating a sample database: Using the following code you can create a sample database:
Step 2: Once the sample database is created create 2 test tables using the following code:
(EMPID INT Primary key ,lastname Varchar(15) NOT Null,FirstName Varchar(15) Not null
,LocationID INT Not null ,[state] Varchar(20) )
Create table TB2test
(GrantID INT Primary key ,GrantName Varchar(15) NOT Null,EMPID INT Not null , Amount INT Not null ,)
Once the tables are created you can test by using the select statement:
Select * from TB2test
Select * from TBtest
Step 3: Inserting the values in the Test tables.
into TBtest values (1,‘Bhatia’,‘Gaurav’,1,‘KA’),(2 ,‘Singh’,‘Sukhdeep’,2,‘SG’)
into TB2test values (001,‘BhatiaGrant’,1,3000),(002 ,‘SinghGrant’,2,4000)
You can run a select statement to see the following values in both tables:
Updating the tables with new values: Now we want to update the amount value by using the location ID. We want to update the amount to 10000 in one of the rows for the amount column where location id = 1.
We will use the Join statement first to select the view we want and then use the update statement to update the data. Using the following statement we can see a view on which both tables are joined through the INNER JOIN clause.
from TBTest AS T inner
join TB2test AS T2 on T.empid=T2.EMPID
On running the above mentioned query we will get the following output:
Now our target was to update the amount column to 10000 where location id = 1 . Hence we will use the following query to update the value to 10000 for location id 1.
Update TB2test set amount =
from TB2test AS T2 inner
join tbtest AS T on t2.EMPID=T.EMPID where T.locationID=1
After you run the above mentioned query the value will be updated to 10000 for locationID =1 using the Joins .We can see the following screenshot:
I hope this article helps you clearing some of the doubts J Please get back to me in case of any questions.