question

InigoMontoya-1790 avatar image
0 Votes"
InigoMontoya-1790 asked EchoLiu-msft edited

Delete With CTE

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



sql-server-transact-sql
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

1 Answer

EchoLiu-msft avatar image
0 Votes"
EchoLiu-msft answered EchoLiu-msft edited

Hi @InigoMontoya-1790,

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.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.