Delete all rows on given condition

Question

user_id  date         fieldid  fieldvalue  fromvalue Tovalue action 
1        2020-01-01     1         a         NULL      0        C
2        2020-01-01     1         a         NULL      0        C
2        2020-01-01     1         a         NULL      0        N
2        2020-01-02     1         a         NULL      0        C

Currently cte query has returns this value. I am trying to delete the the entry both action = c and action = N for an user given date, fieldid, fieldvalue. In this case 1st row and last row must be returned. Row 2nd and 3rd must be deleted since one of the row as N in it.

I am not sure how to add a condition to delete in this case either in self-join or within cte. Any help is much appreciated.

Tag your question with the database you are using.

Gordon Linoff
– Gordon Linoff

2020-05-24 02:35:22 +00:00
Commented May 24, 2020 at 2:35 — Gordon Linoff
– Gordon Linoff, Commented May 24, 2020 at 2:35

Gordon Linoff · Accepted Answer · 2020-05-24 02:36:46Z

3

You can use window functions to filter the rows you want:

select t.*
from (select cte.*,
             sum(case when action = 'N' then 1 else 0 end) over (partition by user_id, date) as n_cnt
      from cte
     ) t
where n_cnt = 0;

You can also write this without window functions:

select cte.*
from cte
where not exists (select 1
                  from cte cte2
                  where cte2.user_id = cte.user_id and
                        cte2.date = cte.date and
                        cte2.action = 'N'
                 );

However, this has two references to the CTE and is likely to have worse performance -- because SQL Server will repeat the logic for the CTE for each reference.

answered May 24, 2020 at 2:36

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Delete all rows on given condition

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related