Update multiple records using JOINS in SQL


SQL joins are very common to fetch the data from multiple tables. One of the other big advantage of joins is that they can be used to update the data in bulk in the database. This is quite helpful to avoid the use of the Cursors or the While loops which can affect the performance badly if the records are in thousands.

Lets take a simple example where you have data of users in a Temporary table. Let’s insert the data into this table :

Image

As above, we are having 4 records which are not in Active state.

Here, you can have the data from any external data source into the Temporary table. I have got the data from the database and will be updating the same in the database using the join with this temporary table for this example. Finally we run the Update query using the JOIN as we can see below :

Image

As we can see, the JOIN query updates the records to StateId = 2 only for the records which we are having matching records in the Temporary table.

I haven’t tried testing the time for the execution of this with large number of records and analysis using the SQL profiler or any other tool but i feel this is certainly a better approach. Similarly it can be used to Delete the records from the table.

Step 3

Advertisements

About Jasminder

.Net developer and blogger by profession, keen to learn new technologies, love nature, music and cricket.
This entry was posted in SQL Server. Bookmark the permalink.

I have a suggestion..

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s