Wednesday, 28 April 2010

Delete from a table which requires lookup in another table

Problem: To delete rows from TableA based on filter condition which requires a lookup on another table TableB.

Solution 1)

DELETE FROM TableA
FROM
TableA as A INNER JOIN
TableB as B on A.ID = b.ID
WHERE B.COL2 = ''someval' and B.COL3 = 'someval'

Solution 2)

DELETE FROM TableA
WHERE ID IN
(SELECT ID FROM TableB where COL2 = ''someval' and COL3 = 'someval')


No comments:

Post a Comment

Note: only a member of this blog may post a comment.