Hello All, I have table with fol 1

[DKL01]

Hello All,

I have table with following columns and rows:

ID FirstName LastName
1 sam john
1 sam john
2 peter stoller
3 pinto burt
3 pinto burt
3 pinto burt

I was wondering how to write a SQL query to delete duplicate rows so that the table looks like :

ID FirstName LastName
1 sam john
2 peter stoller
3 pinto burt

Thanks

 

[bessebo]

Here are the steps I would use:

1) Create a temporary table called #testtable with the same structure as the original table

Create table #testtable
( id int,
firstname varchar(30),
lastname varchar(30))

2) Insert into the temporary table from the originaltable using the "Distinct" keyword.

insert into #testtable
select distinct(id),firstname,lastname
from originaltable

3) This will give you a temporary table with the values you want in it.

4) Then you can truncate the values in the originaltable

Truncate table originaltable

5) Then you can just insert into the originaltable from a select off of the #testtable

Insert into originaltable
select * from #testtable

This should give you the distinct values in the originaltable.

Regards,
Bessebo