Delete using intersect

Question

Delete using intersect

erikpoker

Ten Centuries

Points: 1135
More actions
February 20, 2012 at 5:18 am

#257062

Hi,
Is it possible to delete from a table using intersect?
Something like this:
delete * from Export_Entity
WHERE EXISTS
(SELECT * FROM Export_Entity
INTERSECT
SELECT * FROM #deleterows
)
The reason im asking is that we receive a table (Export_Entity) with an unknown number of columns and we do not know which columns are keys. We take this table, then read this table and do some buisness-logic, and last we want to delete the rows from the Export_Entity that we have read and processed.
The read and processed rows are stored in a temp table #deleterows, so if it was possible do delete the rows that intersect between the two tables that would be awesome.

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

EXISTS returns true or false depending on whether a row exists in the subquery or not, so your example will delete either all the rows in the table (if that INTERSECT returns at least one row) or no rows.

Right track, but try IN rather than EXISTS.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

erikpoker Ten Centuries Points: 1135 More actions · Answer 2

GilaMonster (2/20/2012)
EXISTS returns true or false depending on whether a row exists in the subquery or not, so your example will delete either all the rows in the table (if that INTERSECT returns at least one row) or no rows.
Right track, but try IN rather than EXISTS.

Thanks! However, i cant seem to see how i can make the IN work?

With the IN operator, wouldnt i have to specifiy a column?

HowardW One Orange Chip Points: 29109 More actions · Answer 3

I'm pretty sure you can't accomplish this with INTERSECTS alone - you'd need to break out into dynamic SQL, build your key column list from the table metadata and use EXISTS or a join to avoid having to explicitly define your key columns.

erikpoker Ten Centuries Points: 1135 More actions · Answer 4

HowardW (2/20/2012)
I'm pretty sure you can't accomplish this with INTERSECTS alone - you'd need to break out into dynamic SQL, build your key column list from the table metadata and use EXISTS or a join to avoid having to explicitly define your key columns.

Yeah, this is what i wanted to avoid. But if it cant be done any other way, then i'll just have to start building the dynamic one instead.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

erikpoker (2/20/2012)
GilaMonster (2/20/2012)
EXISTS returns true or false depending on whether a row exists in the subquery or not, so your example will delete either all the rows in the table (if that INTERSECT returns at least one row) or no rows.
Right track, but try IN rather than EXISTS.
Thanks! However, i cant seem to see how i can make the IN work?
With the IN operator, wouldnt i have to specifiy a column?

Yes, you need a column to compare the table you're deleting from to the subquery, regardless of whether you're using Exists or IN

DELETE FROM <SomeTable>

WHERE <PK Column> IN (SELECT <PK column> FROM <SomeTable> INTERSECT <SELECT <Pk Column> FROM #deletedRows)

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

erikpoker Ten Centuries Points: 1135 More actions · Answer 6

GilaMonster (2/20/2012)
Yes, you need a column to compare the table you're deleting from to the subquery, regardless of whether you're using Exists or IN
DELETE FROM <SomeTable>
WHERE <PK Column> IN (SELECT <PK column> FROM <SomeTable> INTERSECT <SELECT <Pk Column> FROM #deletedRows)

Yeah, i suspected that. Problem is there is no PK, and no FKs either. Just a bunch of columns. Not ideal i know, but thats what i got to work with. It looks like a will have to build dynamic sql from the table-metadata then. Comparing every column.. 🙁

HowardW One Orange Chip Points: 29109 More actions · Answer 7

If this is for an ETL process to merge data from a load table, you probably want to consider doing all of this within a MERGE statement (dynamic or otherwise), then you just have to define your key list once and write one statement to do all your inserts, updates and deletes in one pass.