DELETE | UPDATE with JOIN SQL Table

Leave a comment (0) Go to comments

Summary : We can write all your UPDATE, DELETE query same way as we write SELECT Query [*Most of the time],  this means we can use also UPDATE / DELETE using JOIN as well.

I am writing this because, Today I was working with my development team to fix some data. A task was being assigned to a Developer to fix Data and expected time 1 hour and Once I checked with him the item was still in progress.

When I dig into details, this is how the Data was being updated

SQL UPDATE without JOIN CLAUSE

update [Table1]
set feed_id =
(select max(feed_id)
from calls
where [Table1]_id = [Table1].[Table1]_id)
where feed_id = 0
and exists
(select *
from calls
where [Table1]_id = [Table1].[Table1]_id
and feed_id > 0)

Then I just quickly rewritten the this query, which geared up the performance as it decreased the IO cost as the above SQL re-queries twice – once for the update and once for the where.


Update Optimized Query – UPDATE with JOIN Table

update i
set i.feed_id = c.feed_id
from [Table1] i, calls c
where i.feed_id = 0
and c.[Table1]_id = i.[Table1]_id
and c.feed_id > 0

Now the updated Query has single read and then update, which is less number of IO if you compare with upper query.

I was surprised that this small trick is still hidden from developers.

So, let’s understand What is SQL Update Join ?

SQL Update with JOIN helps us to update one table using another table using join condition, as we do in select statements.

Below is an example where I update records by joining tables.

DELETE | UPDATE with JOIN SQL Table sql update with join delete sql join delete from join sql

What all Joins can be used in DELETE / UPDATE / SELECT Statements?

Following Joins can be used with  SQL Statements. Make Sure you use right join for your update / delete statement.

DELETE | UPDATE with JOIN SQL Table sql update with join delete sql join delete from join sql

Here are other Few Examples for

DELETE SQL Statement WITH INNER JOIN

DELETE FROM Table1 TB1 INNER JOIN
Table2 ON Table1.Field2 = Table2.Field2

UPDATE SQL WITH INNER JOIN

UPDATE [myTable2] 

SET 
[myTable2].[FieldA] = 'Hello', 
[myTable2].[FieldB] = 2, 
[myTable2].[FieldC] = 'xxx', 
[myTable2].[FieldD] = 0

FROM  [myTable1] INNER JOIN 
                (myTable2 INNER JOIN [myTable3] 
                              ON [myTable2].[FieldX]=[myTable2].[FieldY]) 
                         ON [myTable1].[FieldZ]=[myTable2].[FieldY] 

WHERE ([myTable2].[FieldY]=1) 
And ([myTable3].[FieldZ]='xxx');
update asset_class_allocation
set to_date = '2010-08-20'
from asset_allocation aa 
join options o on aa.option_uid = o.uid
join product p on o.product_uid = p.uid
where p.product_id in (23568,8629)
and asset_class_allocation.to_date = '2010-07-20'
and asset_class_allocation.allocation_uid = aa.uid;

Is there any other way to UPDATE related Data?

YES, MERGE statement which will allow users to perform insert, update and delete operations in a single statement

MERGE allows you to perform simultaneous UPDATE, INSERT and/or DELETE operations on one table. There are new physical operators that combine these operations so that they can be performed in a single scan rather than multiple scans.

Below are the three different matched clauses in MERGE:

  • WHEN MATCHED THEN
    • Rows that meet the criteria
  • WHEN [TARGET] NOT MATCHED THEN
    • Rows that do not match with another row in the target table
  • WHEN SOURCE NOT MATCHED THEN
    • Rows that do not match with another row in the source table

Please check my Video Tutorial to learn more on MERGE Statement

Conclusion : We can write DELETE / UPDATE SQL Statements by Joining multiple tables.

EOF - DELETE | UPDATE with JOIN SQL Table, SQL Server 2012 , 2008 R2 , 2008 , 2005 , 2000

Leave a Reply

Your email address will not be published. Required fields are marked *


*


You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

Notify me of followup comments via e-mail. You can also subscribe without commenting.