Using Update Statement in SQL when using joins

Hi everyone,

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.

For example:

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:

Use
master

Go

Create
database Test

Go

Step 2: Once the sample database is created create 2 test tables using the following code:

Table 1:

Use Test

Go

Create
table TBtest
(EMPID INT Primary key ,lastname Varchar(15) NOT Null,FirstName Varchar(15) Not null
,LocationID INT  Not null ,[state] Varchar(20) )

Table 2

Use Test

Go

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.

Table 1:

Insert
into TBtest values (1,‘Bhatia’,‘Gaurav’,1,‘KA’),(2 ,‘Singh’,‘Sukhdeep’,2,‘SG’)

Table 2:

Use Test

Go

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

Step 4:

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.

Use Test

Go

Select
*
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 =
‘10000’
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.