Prev: Organizing Solutions and Projects
Next: tuning import of large dataset
From: Keith G Hicks on 5 Jun 2010 11:33
I have an import file that might have duplicates that I don't want to deal
with. I'm creating a temp table from the import file and want to delete the
duplicates from the temp table before proceding with other code.

Here's an example table:

create table #temp (itemnum int, firstname varchar(10), notes varchar(50))

insert into #temp (itemnum, firstname, notes) values (1, 'keith', 'some
note')

insert into #temp (itemnum, firstname, notes) values (1, 'keith', 'some
other note')

insert into #temp (itemnum, firstname, notes) values (2, 'scott', 'some
note')

insert into #temp (itemnum, firstname, notes) values (3, 'bill', 'some
note')

What I need to do is to delete one of the rows that has itemnum = 1 and
firstname = 'keith'. I don't care which one is deleted. It does NOT matter.
The resultant table needs to have itemnum and firstname unique together.

What's the easiest way to do this? I was hoping to do it with a single
delete statement using subqueries but I'm stuck.

Keith




From: Bob McClellan on 5 Jun 2010 11:51
Keith..
if you are using 2005...
here's one way.

alter table #temp
add id int identity

select rn = row_number() over ( partition by itemnum, firstname order by
itemnum, firstname),
*from #temp


delete from #Temp
where id in (
select id from (
select rn = row_number() over ( partition by itemnum, firstname
order by itemnum, firstname),
*
from #temp
)x
where rn <> 1
)

Select *
from #temp

hth,
bob


"Keith G Hicks" <krh(a)comcast.net> wrote in message
news:O$5s5RMBLHA.5584(a)TK2MSFTNGP06.phx.gbl...
>I have an import file that might have duplicates that I don't want to deal
> with. I'm creating a temp table from the import file and want to delete
> the
> duplicates from the temp table before proceding with other code.
>
> Here's an example table:
>
> create table #temp (itemnum int, firstname varchar(10), notes varchar(50))
>
> insert into #temp (itemnum, firstname, notes) values (1, 'keith', 'some
> note')
>
> insert into #temp (itemnum, firstname, notes) values (1, 'keith', 'some
> other note')
>
> insert into #temp (itemnum, firstname, notes) values (2, 'scott', 'some
> note')
>
> insert into #temp (itemnum, firstname, notes) values (3, 'bill', 'some
> note')
>
> What I need to do is to delete one of the rows that has itemnum = 1 and
> firstname = 'keith'. I don't care which one is deleted. It does NOT
> matter.
> The resultant table needs to have itemnum and firstname unique together.
>
> What's the easiest way to do this? I was hoping to do it with a single
> delete statement using subqueries but I'm stuck.
>
> Keith
>
>
>
>

From: Keith G Hicks on 5 Jun 2010 11:59
Cool. Thanks Bob. I knew there had to be a clean way to do this. I was
thinking in the direction of partitioned row numbers but hadn't started
trying that yet. Thanks very much.


"Bob McClellan" <bobmcc(a)tricolift.RemoveThis.com> wrote in message
news:uPbgtbMBLHA.4308(a)TK2MSFTNGP04.phx.gbl...
> Keith..
> if you are using 2005...
> here's one way.
>
> alter table #temp
> add id int identity
>
> select rn = row_number() over ( partition by itemnum, firstname order by
> itemnum, firstname),
> *from #temp
>
>
> delete from #Temp
> where id in (
> select id from (
> select rn = row_number() over ( partition by itemnum, firstname
> order by itemnum, firstname),
> *
> from #temp
> )x
> where rn <> 1
> )
>
> Select *
> from #temp
>
> hth,
> bob
>
>
> "Keith G Hicks" <krh(a)comcast.net> wrote in message
> news:O$5s5RMBLHA.5584(a)TK2MSFTNGP06.phx.gbl...
>>I have an import file that might have duplicates that I don't want to deal
>> with. I'm creating a temp table from the import file and want to delete
>> the
>> duplicates from the temp table before proceding with other code.
>>
>> Here's an example table:
>>
>> create table #temp (itemnum int, firstname varchar(10), notes
>> varchar(50))
>>
>> insert into #temp (itemnum, firstname, notes) values (1, 'keith', 'some
>> note')
>>
>> insert into #temp (itemnum, firstname, notes) values (1, 'keith', 'some
>> other note')
>>
>> insert into #temp (itemnum, firstname, notes) values (2, 'scott', 'some
>> note')
>>
>> insert into #temp (itemnum, firstname, notes) values (3, 'bill', 'some
>> note')
>>
>> What I need to do is to delete one of the rows that has itemnum = 1 and
>> firstname = 'keith'. I don't care which one is deleted. It does NOT
>> matter.
>> The resultant table needs to have itemnum and firstname unique together.
>>
>> What's the easiest way to do this? I was hoping to do it with a single
>> delete statement using subqueries but I'm stuck.
>>
>> Keith
>>
>>
>>
>>
>


From: Erland Sommarskog on 5 Jun 2010 16:56
Keith G Hicks (krh(a)comcast.net) writes:
> What I need to do is to delete one of the rows that has itemnum = 1 and
> firstname = 'keith'. I don't care which one is deleted. It does NOT
> matter. The resultant table needs to have itemnum and firstname unique
> together.
>
> What's the easiest way to do this? I was hoping to do it with a single
> delete statement using subqueries but I'm stuck.

Indeed it is. Here is an example with any extra column as in difference
to Bob's solution:

WITH numbered (rowno) AS (
SELECT row_number() OVER (PARTITION BY itemnum, firstname ORDER BY notes)
FROM #temp
)
DELETE numbered WHERE rowno > 1


--
Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

From: Keith G Hicks on 6 Jun 2010 10:07
Ooooh. I like that. Ididn't know about "with" in that sense. Very neat.


"Erland Sommarskog" <esquel(a)sommarskog.se> wrote in message
news:Xns9D8EE95CAA39AYazorman(a)127.0.0.1...
> Keith G Hicks (krh(a)comcast.net) writes:
>> What I need to do is to delete one of the rows that has itemnum = 1 and
>> firstname = 'keith'. I don't care which one is deleted. It does NOT
>> matter. The resultant table needs to have itemnum and firstname unique
>> together.
>>
>> What's the easiest way to do this? I was hoping to do it with a single
>> delete statement using subqueries but I'm stuck.
>
> Indeed it is. Here is an example with any extra column as in difference
> to Bob's solution:
>
> WITH numbered (rowno) AS (
> SELECT row_number() OVER (PARTITION BY itemnum, firstname ORDER BY
> notes)
> FROM #temp
> )
> DELETE numbered WHERE rowno > 1
>
>
> --
> Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se
>
> Links for SQL Server Books Online:
> SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
> SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
> SQL 2000:
> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
>


 |  Next  |  Last
Pages: 1 2 3
Prev: Organizing Solutions and Projects
Next: tuning import of large dataset