DELETE and UPDATE based on JOIN


This article outlines what is DELETE and UPDATE based on JOIN operations. How to use DELETE based on join operation; and similarly how to use UPDATE based on join operation. Both syntax are not SQL standard syntax.

This article outlines how to use DELETE based on join operation; and similarly how to use UPDATE based on join operation. Both syntax are not SQL standard syntax.

DELETE based on join:

T-SQL allows you to write a nonstandard DELETE syntax based on joins. Join gives you access to attributes of related rows
from another table that you can refer in WHERE clause. This means you can delete rows from one table based on a filter
against attributes in related rows from another table.

For example,


DELETE FROM o
FROM dbo.Orders AS o
JOIN dbo.Customers AS c
ON o.custid = c.custid
WHERE c.country = 'USA';


If you want to stick to standard SQL code you can use sub-queries instead of DELETE based on join.

UPDATE based on join:
Similar to DELETE statement, the UPDATE statement based on join is also supported by T-sql as a nonstandard syntax. In
addition to filtering, the join also gives you access to attributes from other tables that you can use in the column
assignments in the SET clause. The same access to the other table can allow you to both filter and obtain attribute
values from the other table for the assignments.
For example,


UPDATE T1
SET col1 = T2.col1,
col2 = T2.col2,
col3 = T2.col3
FROM dbo.T1 JOIN dbo.T2
ON T1.keycol = T2.keycol
WHERE T2.col4 = 'ABCD'


Comments

No responses found. Be the first to comment...


  • Do not include your name, "with regards" etc in the comment. Write detailed comment, relevant to the topic.
  • No HTML formatting and links to other web sites are allowed.
  • This is a strictly moderated site. Absolutely no spam allowed.
  • Name:
    Email: