CODEWITHSUNDEEP
sqldatabasesql-server-2005
user523709
user523709

Reputation: 21

how to remove a multiple records for same zipcode keeping atleast one record for that zipcode in database table

how to remove a multiple records for same zipcode keeping atleast one record for that zipcode in database table


id zipcode
1  38000 
2  38000 
3  38000 
4  38005
5  38005

i want table with two column with id and zipcode ... my final will be following


id zipcode
1  38000 
4  38005

Upvotes: 1

Views: 260

Answers (6)

Mark SQLDev
Mark SQLDev

Reputation: 539

There's an easier way if you want the lowest ID number. I just tested this:

SELECT 
    min(ID),
    zipcode
FROM #zip
GROUP BY zipcode

Upvotes: 0

Remus Rusanu
Remus Rusanu

Reputation: 294367

with cte as (
 select row_number() over (partitioned by zipcode order by id desc) as rn
 from table)
delete from cte
where rn > 1;

This has the advantage of correctly handling duplicates and offers tight control over what gets deleted and what gets kept.

Upvotes: 1

Jaykay
Jaykay

Reputation: 666

delete from table where id not in (select min(id) from table zipcode in(select distinct zipcode from table));

select distinct zipcode from table - would give the distinct zipcode in the table select min(id) from table zipcode in(select distinct zipcode from table) - would give the record with the min ID for each zip code delete from table where id not in (select min(id) from table zipcode in(select distinct zipcode from table)) - this would delete all the records in the table that are not there as a result of query 2

Upvotes: 0

Christian Severin
Christian Severin

Reputation: 1831

How about

delete from myTable
where id not in (
    select Min( id )
    from myTable
    group by zipcode )

That lets you keep your lowest IDs, which is what you seemed to want.

Upvotes: 6

JonVD
JonVD

Reputation: 4268

To just select that resultset you would use a DISTINCT statement:

SELECT id, zipcode
FROM table
WHERE zipcode IN (SELECT DISTINCT zipcode FROM table)

To delete the other records and keep only one you usea subquery like so:

DELETE FROM table
WHERE id NOT IN 
(SELECT id
FROM table
WHERE zipcode IN (SELECT DISTINCT zipcode FROM table)
)

You can also accomplish this using a join if you perfer.

Upvotes: 1

ceth
ceth

Reputation: 45295

Create temporary table with desired result:

select min(id), zipcode
into tmp_sometable
from sometable
group by zipcode

Remove the original table:

drop table sometable

Rename temporary table:

sp_rename 'tmp_sometable', 'sometable';

or something like:

delete from sometable
where id not in
 (
   select min(id)
   from sometable
   group by zipcode
 )

Upvotes: 0

Related Questions