How do you Delete a particular row only when multiple rows.

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 2000 Forums

Transact-SQL (2000)

How do you Delete a particular row only when multiple rows.

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2006-02-24 : 09:29:15

Philip writes "Example data:
1 a 22
1 b 23
1 c 24

2 a 22

3 a 22
3 b 23

Say I want to delete from the table, all records where column c=22, only if there are multiple record rows (Column a is key field). So I would want to delete (1 b 23, and 1 c 24). Also (3 b 23). But not 2 a 22, since only a single row.

I started developing a query like this but then I am stuck.

Delete all rows from table a
where column c=22 and where
(
select column-a, column-b
from tablea
group by a,b
having (count(a) > 1)
)"

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-02-24 : 10:12:15

Do the following & c whether the Select works for u

Select * from UrTbl as T1 where  (
c != 22 or 1 > (select count(*) from UrTbl as T2 where T1.a = T2.a group by a))

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-02-24 : 10:14:14

Also refer this for deleting duplicate records
http://sqlteam.com/forums/topic.asp?TOPIC_ID=6256

Madhivanan

Failing to plan is Planning to fail

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2006-02-24 : 10:22:58

see: http://weblogs.sqlteam.com/jeffs/archive/2004/10/07/2190.aspx

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-02-24 : 10:24:34

Madhi,

This is not "Typical Duplicate Records" issue, even if its worded as so

Delete UrTbl from 
UrTbl as T1 where  (
c != 22 or 1 > (select count(*) from UrTbl as T2 where T1.a = T2.a group by a))

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources