Delete using subquery and intersect?

Question

I am trying to delete using the below query but unfortunately, all the records are deleted

DELETE [dbo].[devicelinks] FROM (SELECT * FROM [dbo].[devicelinks] WHERE PID = 7 INTERSECT SELECT * FROM ASC.dbo.DEVICE_LINK WHERE PID = 7)

How to properly use the subquery to delete the corresponding records from the source table?

No keys or constraints in either of the two tables.

Giorgos Betsos · Accepted Answer · 2017-10-16 05:09:28Z

2

You can use EXISTS to detect the records to be deleted:

DELETE d1 FROM [dbo].[devicelinks] d1 WHERE PID = 7 AND EXISTS (SELECT * FROM ASC.dbo.DEVICE_LINK AS d2 WHERE d1.PID = d2.PID AND d1.[DEVICEID] = d2.[DEVICEID] AND ... rest of the fields here )

answered Oct 16, 2017 at 5:09

Giorgos Betsos

72.3k10 gold badges69 silver badges103 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

RegBes Over a year ago

you may get performance issues with a EXISTS and nested join on larger datasets.

Martin Smith Over a year ago

@RegBes - You may get performance issues with any query against larger datasets. EXISTS is not inherently worse than your answer which I assume you are trying to imply. SQL Server has a semi join logical operator that can do this at least as efficiently as a regular join.

RegBes · Accepted Answer · 2017-10-16 05:56:04Z

1

Give this a try

delete [dbo].[devicelinks] from [dbo].[devicelinks] inner join ASC.dbo.DEVICE_LINK on [dbo].[devicelinks].PID = ASC.dbo.DEVICE_LINK.PID where [dbo].[devicelinks].PID = 7

answered Oct 16, 2017 at 5:56

RegBes

5693 silver badges11 bronze badges

5 Comments

Bodhi Over a year ago

i can try this but i need to check more than one column in my binding list

RegBes Over a year ago

You can add on the the join clause like this " and [dbo].[devicelinks].MyField= ASC.dbo.DEVICE_LINK.MyField" You cha have as many of these as required

Bodhi Over a year ago

Which is recommended , subquery or join

RegBes Over a year ago

I checked and @Martin Smith is right the query optimizer creates the same plan for both, so it ends up being up a matter of style, i prefer the join as it reads easier for me.

Bodhi Over a year ago

Usually, i prefer subquery as I find it hard to master and the diversity it provides.

Collectives™ on Stack Overflow

Delete using subquery and intersect?

2 Answers 2

2 Comments

5 Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

5 Comments

Related