Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Delete records scenario

Status
Not open for further replies.
Mar 6, 2008
56
US
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

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

Code:
;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
 

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
 
Did you try what I suggested?

Code:
-- 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
 
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
 
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
 
I think I showed the correct syntax (for SQL Server anyway).
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top