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

Transact-SQL (2000)

Delete Query

Author

Topic

paul.rowling
Yak Posting Veteran

81 Posts

Posted - 2003-03-07 : 06:13:45

Hi,

I have a table which contains a customer_id column and a catch column which is an identity column with incremental values.

I want to be able to delete all those records where the customer_id = customerid (ie are duplicates) and where the catch column is greater than the catch column (therefore leaving me with only one of the duplicate pair.)

I have been trying to use aliases to do the job but the query just keeps deleting all records instead of one of the duplicates.

How do I achieve this?

Cheers

Paul

Robwhittaker
Yak Posting Veteran

85 Posts

Posted - 2003-03-07 : 06:28:07

If I understand you've got some thing like

Customer_id Catch_column
1 1
1 2
2 3
2 4
2 5
3 6

and you want to be left with

Customer_id Catch_column

1 2
2 5
3 6

What you need is something like

delete from table a
where a.catch_column <> (Select max(b.catch_column)
from table b
where a.customer_id = b.customer_id)

Always do a "select *" with your where clause before the delete and check it returns what you're wanting to delete

samsekar
Constraint Violating Yak Guru

437 Posts

Posted - 2003-03-07 : 06:37:18

This will delete all the old duplicate customer ids and will retain max catch.

delete from testdelete where catch not in
(select max(catch) from testdelete group by customerid having count(customerid)>1 )

___________________________________________________________________________
Success is not a destination that you ever reach. Success is the quality of your journey.

paul.rowling
Yak Posting Veteran

81 Posts

Posted - 2003-03-07 : 07:00:44

Thanks Rob!!

As a side line how would I structure the query to find records where only the customerid = customerid i.e ignoring the catch column?

Cheers

Paul

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2003-03-07 : 07:03:25

select * from tablea a inner join tableb b on a.customerid = b.customerid

Jay White
{0}

paul.rowling
Yak Posting Veteran

81 Posts

Posted - 2003-03-07 : 09:55:01

Hi Jay,

Thanks for your post but it returns all the records in the table not the duplicates based on customerid.

Cheers

Paul

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2003-03-07 : 11:00:41

I'm sorry, I don't understand the requirements.

Jay White
{0}

paul.rowling
Yak Posting Veteran

81 Posts

Posted - 2003-03-10 : 05:46:34

Hi,

In reply to Rob Whittakers post, I tried the query using a select statement at first and it works great, but when I use it as a delete query and I parse the query I get the following error:

Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near 'a'.

I don't understand this as it works with a select statement.

Any ideas would be greatly appreciated

Cheers

Paul

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources