CODEWITHSUNDEEP
sqlsql-servert-sql
user10189172
user10189172

Reputation:

SQL Server - fast way to delete records based on a table join condition

Normally I would truncate the data like this:

truncate table DST.BreaksDST

However, I need to only delete data in which a join to another table contains a source of 'DST'

My SELECT statement looks like this:

select 
    dstid, dst.breakid 
from 
    DST.BreaksDST dst 
join 
    base.breaks b on dst.BreakId = b.BreakId
where 
    b.source = 'DST'

So I know that If I use something like DELETE .... IN (select dst.breakid .....)

That would be very slow, right?

What is a better way?

Upvotes: 1

Views: 282

Answers (1)

Lukasz Szozda
Lukasz Szozda

Reputation: 175994

You could use DELETE [FROM] ... FROM JOIN:

DELETE dst
FROM DST.BreaksDST dst 
JOIN base.breaks b
   ON dst.BreakId = b.BreakId
WHERE b.source = 'DST';

db<>fiddle demo

Please note that doubleFROM is not an error:

DELETE FROM dst
FROM BreaksDST dst 
JOIN breaks b
   ON dst.BreakId = b.BreakId
WHERE b.source = 'DST';

Upvotes: 4

Related Questions