Remove or keep data in column - SQL

user_3628074 0
Viewed 96

I have a list of values in the column that I need to remove or to keep based on the following criteria:

  1. If the same id have the following types (on the same ID): 1000, 1100, 1200, 1300 and any other types such as 700, 800, 900 and etc. I need to remove the these types (1000, 1100, 1200, 1300)

  2. But if the ID has only types: 1000, 1100, 1200, 1300 and no other types then keep them

Source

ID       | TYPE
40208001 | 700
40208001 | 1100
40209017 | 900
40209017 | 800
40209017 | 910
40209017 | 1200
40210494 | 1100
40210494 | 1000
40210494 | 1200
40210551 | 400
40210551 | 1200
40210767 | 1200
40210767 | 800
40211724 | 1300
40211724 | 900
40211724 | 500

WHAT I WANT:

ID       | TYPE
40208001 | 700
40208001 |
40209017 | 900
40209017 | 800
40209017 | 910
40209017 |
40210494 | 1100
40210494 | 1000
40210494 | 1200
40210551 | 400
40210551 |
40210767 |
40210767 | 800
40211724 |
40211724 | 900
40211724 | 500
sql-server sql
4 Answers

Use an EXISTS condition in your WHERE clause to find such rows:

UPDATE t
SET TYPE = NULL
WHERE TYPE IN (1000, 1100, 1200, 1300)
AND EXISTS (
    SELECT 1
    FROM t AS x
    WHERE ID = t.ID
    AND TYPE NOT IN (1000, 1100, 1200, 1300)
)

Demo on DB Fiddle

If you want to SELECT the data then you could rewrite the above as an OUTER APPLY:

SELECT ID, CASE
    WHEN TYPE IN (1000, 1100, 1200, 1300) AND hasother = 'y' THEN NULL
    ELSE TYPE
END AS TYPE
FROM t
OUTER APPLY (
    SELECT TOP 1 'y'
    FROM t AS x
    WHERE ID = t.ID AND TYPE NOT IN (1000, 1100, 1200, 1300)
) OA(hasother)
user_870150

You can use below query to achieve the result. 

select t1.ID, (case when length(t1.type) > min_l then NULL else t1.type end) type
from test t1
join 
(select ID,length(Min(type)) min_l from test group by ID) t2 
on t1.ID = t2.ID

SQL Fiddle

user_39270030

you can also use a query like below. Logic is to find a set of ids which have a value in the thousands range and then left join this with orginal table with a case condition

See working demo

; with idsWithTypeInThousands as 
(
select id from sourcetbl where type in (1000, 1100, 1200, 1300)
group by id 
)

select s.id, case when  i.id is not null and type not in (1000, 1100, 1200, 1300)  then null else type end 
from sourcetbl s left join idsWithTypeInThousands i
on i.id=s.id
user_11232260

try this:

    Declare @ListValues TABLE(Id int, [Type] int)

    INSERT INTO @ListValues
    SELECT 40208001,700  Union All
    SELECT 40208001,1100 Union All
    SELECT 40209017,900  Union All
    SELECT 40209017,800  Union All
    SELECT 40209017,910  Union All
    SELECT 40209017,1200 Union All
    SELECT 40210494,1100 Union All
    SELECT 40210494,1000 Union All
    SELECT 40210494,1200 Union All
    SELECT 40210551,400  Union All
    SELECT 40210551,1200 Union All
    SELECT 40210767,1200 Union All
    SELECT 40210767,800  Union All
    SELECT 40211724,1300 Union All
    SELECT 40211724,900  Union All
    SELECT 40211724,500

    ;with cte
    As
    (

        Select Id
        ,SUM(CASE WHEN Type<1000 THEN 1 ELSE 0 END)As Type1Count
        ,SUM(CASE WHEN Type<1000 THEN 0 ELSE 1 END) As Type2Count
            from @ListValues
            Group by Id
    )

    Update l
    set Type=NULL
    from cte c
    JOIN @ListValues l on c.Id=l.Id
    WHERE Type2Count>0 and Type1Count>0 and l.Type>1000

    Select * from @ListValues order by id

Note: Condition on Type column can be changed as per your requirement. From your question description, i derived the conditions (type>1000 and type<100).

user_55585260
Related