Delete Query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Delete Query

Author

Topic

kneekill
Yak Posting Veteran

76 Posts

Posted - 2009-10-15 : 05:48:58

Hi

I need to delete rows from table based on below condition
if the an 'ID' consists of both codes 'q' and 'r' then need to delete the row with code 'r'

create table #test(id int , code varchar(100))

insert into #test (id,code)
select 1,'q' union all
select 1,'q' union all
select 2,'r' union all
select 2,'r' union all
select 3,'q' union all
select 3,'q' union all
select 3,'r' union all
select 4,'q' union all
select 4,'r' union all
select 4,'r' union all
select 4,'s'

my final result will be:
ID Code
1 q
1 q
2 r
2 r
3 q
3 q
4 q
4 s

thanks

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-10-15 : 06:31:52

[code]
DELETE t
FROM #test t
INNER JOIN
(
SELECT id
FROM #test
WHERE code IN ('q', 'r')
GROUP BY id
HAVING MIN(code) <> MAX(code)
) d ON t.id = d.id
WHERE t.code = 'r'
[/code]

KH
[spoiler]Time is always against us[/spoiler]

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-10-15 : 06:32:53

Something like this?
delete t
from #test t
where code='r'
and exists(select * from #test t2 where t2.id=t.id and t2.code='q')

No, you're never too old to Yak'n'Roll if you're too young to die.

YellowBug
Aged Yak Warrior

616 Posts

Posted - 2009-10-15 : 06:37:05

Here's another option:

delete #test
where code = 'r'
and id in (
select ID from #test where code = 'q'
intersect
select ID from #test where code = 'r')

kneekill
Yak Posting Veteran

76 Posts

Posted - 2009-10-15 : 06:37:50

Thanks a lot khtan , webfred and YellowBug all three of your solutions worked. And that too in only one statement.

my bad solution that i have come up with ;-)

select * into #test_A11 from #test_A where code='r' or code='q'
delete from #test where id in (select Id from #test_A111 group by Id having count( ID)>1) and code='r'

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2009-10-15 : 06:56:58

Here's my contribution:

delete b
from #test a
  inner join #test b
    on a.id = b.id and b.code = 'r'
where a.code = 'q'

- Lumbago
http://xkcd.com/327/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources