Delete from a table using CTE

Question

1.00/5 (1 vote)

See more:

Good evening

I had help with a script below to find duplicate input within 20min and delete them the top part works fine but when I run the Delete statement I am getting the following error

(2 rows affected)
Msg 208, Level 16, State 1, Line 27
Invalid object name 'table_nameCTE'.

Completion time: 2022-08-22T20:23:44.1679187+00:00

SQL

USE [7X2TT]
go


;WITH table_nameCTE AS  
(  
   SELECT 
       COL_CALL, COL_TIME_ON, 
       ROW_NUMBER() OVER (PARTITION BY COL_CALL  ORDER BY COL_TIME_ON) AS ID  
   FROM 
       [dbo].[TABLE_HRD_CONTACTS_V01] 
)
SELECT
    a.ID, a.COL_CALL, a.COL_TIME_ON, T.ID, T.COL_CALL,T.COL_TIME_ON
FROM
    table_nameCTE AS a 
    CROSS APPLY
    (
        SELECT ID, COL_CALL, COL_TIME_ON 
        FROM table_nameCTE  
        WHERE ABS(DATEDIFF(minute, a.COL_TIME_ON, COL_TIME_ON)) <= 20
        AND COL_CALL = a.COL_CALL AND ID<>A.ID
    ) AS T
WHERE 
    a.COL_CALL = '2E0FNU'
;

DELETE FROM table_nameCTE WHERE ID > 1

What I have tried:

DELETE FROM table_nameCTE WHERE ID > 1

Posted 22-Aug-22 10:29am

Mes 501

Updated 25-Aug-22 6:19am

Richard Deeming

v2

Add a Solution

3 solutions

Solution 1

WITH common_table_expression (Transact-SQL) - SQL Server | Microsoft Docs[^]:
Specifies a temporary named result set, known as a common table expression (CTE). This is derived from a simple query and defined within the execution scope of a single SELECT, INSERT, UPDATE, DELETE or MERGE statement.

As the documentation clearly states, you cannot use a CTE from more than one statement.

Your CTE is valid for the SELECT statement. If you want to use it for the following DELETE statement, you will need to declare it again.

SQL

WITH table_nameCTE AS  
(  
   SELECT 
       COL_CALL, COL_TIME_ON, 
       ROW_NUMBER() OVER (PARTITION BY COL_CALL  ORDER BY COL_TIME_ON) AS ID  
   FROM 
       [dbo].[TABLE_HRD_CONTACTS_V01] 
)
SELECT
    a.ID, a.COL_CALL, a.COL_TIME_ON, T.ID, T.COL_CALL,T.COL_TIME_ON
FROM
    table_nameCTE AS a 
    CROSS APPLY
    (
        SELECT ID, COL_CALL, COL_TIME_ON 
        FROM table_nameCTE  
        WHERE ABS(DATEDIFF(minute, a.COL_TIME_ON, COL_TIME_ON)) <= 20
        AND COL_CALL = a.COL_CALL AND ID <> A.ID
    ) AS T
WHERE 
    a.COL_CALL = '2E0FNU'
;

WITH table_nameCTE AS  
(  
   SELECT 
       COL_CALL, COL_TIME_ON, 
       ROW_NUMBER() OVER (PARTITION BY COL_CALL  ORDER BY COL_TIME_ON) AS ID  
   FROM 
       [dbo].[TABLE_HRD_CONTACTS_V01] 
)
DELETE FROM table_nameCTE WHERE ID > 1;

Posted 22-Aug-22 22:22pm

Richard Deeming

Solution 2

thank you Richard - if I run the the 2nd CTE it will delete more data.

what I want to achieve with the CTE is check my Table and look for duplicate that are say with 20min and delete one them. how can I achieve that with just one CTE query? thank you in advance

Posted 24-Aug-22 8:33am

Mes 501

Comments

CHill60 25-Aug-22 11:47am

If you want to respond to a post use the "Have a Question or Comment?" link next to it. The poster will be notified, whereas by posting a solution you will not get their attention - just a lot of downvotes.

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CHill60 · Accepted Answer · 2022-08-25T06:19:00

Quote:
what I want to achieve with the CTE is check my Table and look for duplicate that are say with 20min and delete one them. how can I achieve that with just one CTE query? thank you in advance

I don't think you can do it with a single cte. I also wouldn't use a cross apply but instead use a Dimension table that contained all the possible 20 minute "slots" in my data. You can keep this as a cte or store the information in a temporary table. I would then have a query (or cte) that gets which "slot" each of my data rows is in, then do the partitioned row number on that. You say you only want to delete one so in my example below I only delete where the partitioned row number equals 2

SQL

declare @demo table (COL_CALL varchar(10), COL_TIME_ON datetime);
insert into @demo (COL_CALL, COL_TIME_ON) values
('2E0FNU', '2022-08-25 10:00:00'),
('2E0FNU', '2022-08-25 10:19:00'),
('2E0FNU', '2022-08-25 10:21:00'),
('2E0FNU', '2022-08-25 10:58:00'),
('2E0FNU', '2022-08-25 10:59:00'),
('2E0FNU', '2022-08-25 11:00:00'),
('2E0FNU', '2022-08-25 12:00:00'),
('2E0FNU', '2022-08-25 12:10:00');

select * from @demo

-- the time range we are dealing with
declare @start datetime = (select min(COL_TIME_ON) from @demo);
declare @end datetime = (select dateadd(minute, 20, max(COL_TIME_ON)) from @demo);

-- Use a recursive CTE to generate a dimension table with all the possible timeslots in it
-- up to 40 minutes past the last time in the table. This is so we don't have to handle the null value
WITH q AS
(
    SELECT  @start AS datum, 1 as slot
    UNION ALL
    SELECT  dateadd(minute, 20, datum), slot + 1
    FROM    q
    WHERE dateadd(minute, 20, datum) < dateadd(minute, 20, @end)
), cte1 as
(
	SELECT datum, slot, lead(datum) over (order by slot) as nextdatum
	FROM q
), cte2 as 
(
	select *, row_number() over (partition by slot order by slot) as rowc 
	from cte1 a
	inner join @DEMO b on b.COL_TIME_ON between a.datum and a.nextdatum
)
delete 
from cte2 
where rowc = 2

select * from @demo