Delete records scenario

[techytechy]

Hi Guys,

Need query help with this scenario.

T
------------------------------------
ID Code Name status
--------------------------------------
1 1 Name1 Good
2 2 Name2 Bad
3 1 Name1 Bad
...
...
-----------------------------------
when a record with the status = Good exists and if there are more records with the same Name and Code then those records should be deleted including the one with status = good.

In the above ID = 1, ID = 3 need to be deleted as they have same Code and Name and status of one of them is 'Good'.

Thanks

 

[markros]

Try this code (SQL Server 2005 and up) - you can use very similar idea for SQL Server 2000 - just use GROUP BY Name then.

;with cte_check as (select Code, Name, Status, count(*) over (partition by Name) as TotalCount, sum(case when Status = 'Good' then 1 else 0 end) over (partition by Name) as GoodStatusesCount from myTable)

delete from cte where TotalCount > GoodStatusesCount and GoodStatusesCount >=1

 

[techytechy]

Why this doesnt work?
-------------------
declare @test_ab table
( code int ,
Names varchar(128),
status varchar(128)
)

insert @test_ab
select * from

(select 1 as Code, 'Name1' as Names, 'Good' as status
UNION all
select 1 as Code, 'Name1' as Names, 'Bad' as status
UNION all
select 2 as Code, 'Name2' as Names, 'Bad' as status) x

select * from @test_ab

DELETE
FROM @TEST_AB WHERE (Code, Names) in
(

--select * from (

SELECT
B.code, B.names FROM @TEST_AB B,(SELECT code, names, COUNT(DISTINCT STATUS) as t FROM @TEST_AB
GROUP BY code, names
HAVING COUNT(DISTINCT STATUS)>1
) A
WHERE
A.code=B.code
AND
A.names=B.names
and Status = 'Good'
)
------------------
But this one works if I remove delete statement, but my requirement is to delete those records.
---------------
declare @test_ab table
( code int,
Names varchar(128),
status varchar(128)
)

insert @test_ab
select * from

(select 1 as Code, 'Name1' as Names, 'Good' as status
UNION all
select 1 as Code, 'Name1' as Names, 'Bad' as status
UNION all
select 2 as Code, 'Name2' as Names, 'Bad' as status) x

select * from @test_ab
--
--DELETE
--FROM @TEST_AB WHERE (Code, Names) in
--(

select * from (

SELECT
B.code, B.names FROM @TEST_AB B,(SELECT code, names, COUNT(DISTINCT STATUS) as t FROM @TEST_AB
GROUP BY code, names
HAVING COUNT(DISTINCT STATUS)>1
) A
WHERE
A.code=B.code
AND
A.names=B.names
and Status = 'Good'
) x
---------------

Thanks

 

[markros]

Did you try what I suggested?

-- Test query
declare @test_ab table( code int ,Names varchar(128),status varchar(128))

insert @test_ab
select * from 
(select  1 as Code, 'Name1' as Names, 'Good' as status
UNION all select  1 as Code, 'Name1' as Names, 'Bad' as status
UNION all select  2 as Code, 'Name2' as Names, 'Bad' as status) x

select * from  @test_ab

delete T from @test_ab T inner join 
(select Names, COUNT(distinct(Status)) as StatusCount, 
SUM(Case when Status = 'Good' then 1 end) as GoodStatusCount from @test_ab
 group by Names having COUNT(distinct(Status))>=2 and SUM(Case when Status = 'Good' then 1 end) >=1) X on T.Names = X.Names 
 
 select * from  @test_ab

 

[techytechy]

checked your code with cte but it doesnt work: heres the result:
--------------
declare @test_ab table( code int ,Names varchar(128),status varchar(128))

insert @test_ab
select * from
(select 1 as Code, 'Name1' as Names, 'Good' as status
UNION all select 1 as Code, 'Name1' as Names, 'Bad' as status
UNION all select 2 as Code, 'Name2' as Names, 'Bad' as status) x

select * from @test_ab

;with cte_check as
(select Code, Names, Status, count(*) over (partition by Names) as TotalCount,
sum(case when Status = 'Good' then 1 else 0 end) over (partition by Names) as GoodStatusesCount from @test_ab)

delete from cte_check where TotalCount > GoodStatusesCount and GoodStatusesCount >=1
-------------------

Your query with out CTE works fine. The problem in my query was Sql server does not like "WHERE (Code, Names) in" in my query. Though this type of where condition works in Oracle.

Thanks

 

[techytechy]

Though your query below works fine but I didnot understand Why it should be "delete T from @test_ab T"
Why not "delete from @test_ab T" work?

delete T from @test_ab T inner join
(select Names, COUNT(distinct(Status)) as StatusCount,
SUM(Case when Status = 'Good' then 1 end) as GoodStatusCount from @test_ab
group by Names having COUNT(distinct(Status))>=2 and SUM(Case when Status = 'Good' then 1 end) >=1) X on T.Names = X.Names

 

[markros]

I think I showed the correct syntax (for SQL Server anyway).

 

[techytechy]

Thanks so much

 

[markros]

You're welcome, mark the response that helped you.