How to Delete top 1 item after using CTE (Common Table Expression)

Question

How to Delete top 1 item after using CTE (Common Table Expression)

King Java 790

I am trying to delete top 1* item after using CTE.

I have the bottom T-SQL statement which works fine with select statement on the second part.

with cte as
(
    select *, 
           email_cnt = count(*) over (partition by "User"),
		   duplicate_Yes =	case when count(AccessToAllLocations) over (partition by "User") > 1
									and 
									count("Location") over (partition by "User") > 1
                                then 1
                                else 0
                                end
    from   [dbo].[tblPBIGroup_CA]
)
select top 1*  from cte c
where  email_cnt > 1
and    duplicate_Yes = 1
order by DateLoad asc

But, I am trying to delete not select.

How do I modify to make it work?

Accepted answer

1 additional answer

Your answer

Answer 1

Michael Taylor 60,161

The CTE is running a query so you cannot delete from that. Use the Delete-Select syntax of SQL to delete from the table the results of running the query.

;WITH cte (...)
DELETE FROM [tblPBIGroup_CA]
FROM [tblPBIGroup_CA] t
JOIN (SELECT TOP 1 * FROM cte...) c ON <key joining tblPBIGroup_CA to itself>

King Java 790 Reputation points

2025-04-15T20:15:07.77+00:00
@Michael taylor Thanks for help. Can you clarify the last part where joins take place?

And I am little bit confused why there are two deletes:

DELETE FROM [tblPBIGroup_CA] FROM [tblPBIGroup_CA] t
Michael Taylor 60,161 Reputation points

2025-04-15T20:37:04.11+00:00
The join is going to be on the primary key(s) of your table. Your CTE should return only the columns you need to uniquely identify the row(s) you want to delete. Most DBAs would reject any usage of select *. That is always bad. I'm assuming you did that just for this post. If not then update your CTE to only return the column(s) you need to uniquely identify the rows to delete. Besides the columns needed for your WHERE clause then it would generally include the primary key(s) of the table. Since we have no knowledge of your DB structure we cannot answer what that might look like. Looking at the query then potentially the User column, but that may delete too much.

For example purposes though let's assume your CTE returns back the unique ID column of the row you want to delete. That is what the join will use.

FROM table t JOIN (cte-query) x ON t.Id = x.Id

That would join the results of your CTE, which you said properly identifies the row to delete, back to the table with the data that you want to delete from.

And I am little bit confused why there are two deletes:

There aren't 2 deletes here. This is the syntax for Delete Select as I linked to in my original post. For a normal delete you follow the DELETE FROM with the WHERE clause. But for a Delete Select you have to specify a query. That query is what goes after the DELETE FROM. It is basically a regular SELECT minus the SELECT clause as that isn't needed for the deletion.

DELETE FROM someTable --SELECT * FROM someTable WHERE ...

Hence the second FROM is the start of the query that identifies the rows to remove from the table specified in the DELETE.

Answer 2

It is not clear to me what you want to delete, but Michael is wrong. You can delete from the CTE (as long as SQL Server can map back to a single table). No need for the join.

Maybe this is what you want:

with cte as
(
    select *, 
           email_cnt = count(*) over (partition by "User"),
		   duplicate_Yes =	case when count(AccessToAllLocations) over (partition by "User") > 1
									and 
									count("Location") over (partition by "User") > 1
                                then 1
                                else 0
                                end
    from   [dbo].[tblPBIGroup_CA]
), nextcte as (
   select top 1*  from cte c
   where  email_cnt > 1
   and    duplicate_Yes = 1
   order by DateLoad asc
)
delete nextcte

Share via

How to Delete top 1 item after using CTE (Common Table Expression)

1 additional answer

Your answer