Delete With CTE

Question

I have this DDL which works but only when ks is not null for every order. In my sample DDL below ks is null for every row in the table for ordernumber lst-123.

This is my current DDL -> which the Delete CTE works perfect when ks exists for every order, however if ks is null for all rows for an ordernumber I do not want to delete any rows in the table (we want to hold for further analysis).

Create Table Information
(
    ordernumber varchar(100)
    ,ks varchar(250)
)

Insert Into Information Values
('abc-123', 'lmn1'),
('abc-123', ''),
('abc-123', ''),
('lst-123', ''),
('lst-123', ''),
('lst-123', ''),
('lst-123', ''),
('lst-123', ''),
('lst-123', '')

Select *
INTO Helper
FROM Information

;WITH cte AS (
    SELECT 
    ordernumber,
    ks, 
    row_number() OVER(PARTITION BY  ordernumber, ks ORDER BY ordernumber) AS [rn]
    FROM Helper
)
Delete FROM cte 
WHERE [rn] > 1 
AND ks IS NOT NULL

Select * from Helper
Drop Table Helper
Drop Table Information

After the Delete CTE has executed this is my desired output:

ordernumber ks
abc-123        lmn1
abc-123 
lst-123 
lst-123 
lst-123 
lst-123 
lst-123 
lst-123 

As we see, we deleted 1 row for abc-123 because ks is not null for every row for abc-123, and we kept all rows in the database for lst-123 because ks is null for all rows in the database.

How would I write a delete query to hyandle this?

SQL-Server 2016

Answer 1

Hi @Johnathan Simpson ,

Your data is '' instead of NULL,'' is not the same as NULL, you cannot use IS NOT NULL to filter '' values.For '' values, you need to use =, <> to filter.

Please try:

 ;WITH cte AS (  
     SELECT   
     ordernumber,  
     ks,   
     row_number() OVER(PARTITION BY  ordernumber, ks ORDER BY ordernumber) AS [rn]  
     FROM Helper  
 )  
 Delete FROM cte   
 WHERE [rn] > 1   
 AND ordernumber in(SELECT ordernumber FROM Helper WHERE ks <>'')    

If you have any question, please feel free to let me know.

Regards
Echo

---

If the answer is helpful, please click "Accept Answer" and upvote it.