CODEWITHSUNDEEP
sqlsql-serversql-server-2008-r2
NooooobDeveloper
NooooobDeveloper

Reputation: 71

How to delete records that match a condition and leave one?

I have a stored procedure where I want to delete all records but one that match a certain condition. I can't seem to figure it out.

I have the following temporary table:

CREATE TABLE #TEMP_RESULTS(
    code NVARCHAR(50),
    newCode NVARCHAR(50),
    result NVARCHAR(10)
)

Then I have code that inserts data in that table. Basically, the records look like that:

code  newCode   result
A1      B1       <15
A1      B2       <20
A1      B3       <30 -- in this case one of the three A1s should stay in the table
A2      B4        25 -- this is staying
A2      B5       <45
A3      B6       <22

After that, I am trying to:

DELETE TR
FROM #TEMP_RESULTS TR
WHERE   TR.result LIKE '<%' -- here I need to add another condition that can leave one row in the table

EDIT: Forgot to mention, that searched on the Internet, but most of the topics were about removing duplicates or selecting TOP records.

EDIT2: This is the desired output:

code  newCode   result
A1      B1       <15
A2      B4        25 -- this is staying
A2      B5       <45
A3      B6       <22

This is done, because later I would like to update the result to something else. There is no constraint and no grouping.

Upvotes: 0

Views: 146

Answers (1)

Felix Pamittan
Felix Pamittan

Reputation: 31879

Try using a CTE with a WHERE clause:

;WITH CTE AS(
    SELECT *,
        rn = ROW_NUMBER() OVER(PARTITION BY code ORDER BY (SELECT NULL))
    FROM #TEMP_RESULTS  
    WHERE result LIKE '<%'
)
DELETE FROM CTE WHERE rn > 1

Modify the ORDER BY depending on which row you want to stay.

Upvotes: 1

Related Questions