I have a table with the below structure. I would like to delete all of the records that records that match
ColA for any of the
ColB's that have a
ColC that has an even-numbered value. But only for the first instance of
ColA. (In this example all of the AA records would be deleted)
ColB are unique and sorted in alphabetical order.
ORderByCol is part of the query that builds this table:
ROW_NUMBER() OVER(PARTITION BY ColA ORDER BY ColB)
ColA ColB ColC OrderByCol AA A 8 1 AA B 3 2 AA F 5 3 BB B 7 1 BB D 9 2 CC A 1 1 CC Q 5 2
I can do this with an ColA in a subquery, but that seems like a lot of work and it is really slow.
DELETE T FROM Table1 AS T WHERE T.ColA IN ( SELECT DISTINCT ColA FROM Table1 WHERE EXISTS (SELECT * FROM Table1 WHERE ColC % 2) AND ORderByCol = 1 )
So the query will give me a list of all unique ColA values that have the first item not primary, but it is really slow. I'm sure there is a faster way to do this.
EDIT: If someone knows a better way to do what I am asking that would be awesome! I always like learning new ways to do things in SQL. However I found out why my query was slow. I didn't have an index on the table because I thought that table variables (@table1) tables couldn't have indexes. Once I put in an index on my table it reduced my run time from 1 min 20 sec to 2 sec.
Thank you to every one who has helped out with this.