i am having clomns say c1,c2,c3,c4
if two rows contains same values in c2 and c3. i need to delete one row.
i.e say if row1 contains c2 value A and c3 value is B
if there is other row which contains c2 Value A and c3 value B.
I need to remove one record.

please help me. I am sick of it.

Regards
vamsi.


vamsi

__________________
vamsi Krishna G
mobile: 91 + 9986023966.
124 Puttappa layout new Tippasandhra,
bangalore.
Inida

This may help you.


Hi - I have uploaded into a temp table approx. 300K of records, within this table there are 2 duplicate records. I want to delete these before I insert them into a base table and put a PKey on Col1

Col1
X0001
Y0000
X0001
Y0000

It does not matter which one of the duplicate records I delete. How can I do this with SQL??

TIA
Ian


pgtips
Advanced Member



United Kingdom
1183 Posts
 Posted - 08/29/2003 : 06:14:25 AM


--------------------------------------------------------------------------------

Since you know that you only have 1 duplicate of each value, you can just use the ROWCOUNT option to prevent it deleting more than one row, like this:


SET ROWCOUNT 1
DELETE FROM <<tablename>>
WHERE Col1='X0001'
DELETE FROM <<tablename>>
WHERE Col1='Y0000'




ioates
New Member



United Kingdom
60 Posts
 Posted - 08/29/2003 : 06:25:11 AM


--------------------------------------------------------------------------------

Thanks for the reply unfortunately, the duplicate values could change next time I update my base table, So a dummy scenario could be no duplicate values next time or 1 or more duplicates.


pgtips
Advanced Member



United Kingdom
1183 Posts
 Posted - 08/29/2003 : 06:37:39 AM


--------------------------------------------------------------------------------

OIC you just want a general solution to this problem. The usual method is to
1. select distinct records into a temp table,
2. clear the original table
3. copy records back from temp table to original table

I'm sure you'll find plenty of examples if you search google with something like "SQL Server delete duplicate"

rgds
Phil


ioates
New Member



United Kingdom
60 Posts
 Posted - 08/29/2003 : 06:44:43 AM


--------------------------------------------------------------------------------

Thanks Phil
This makes sense

Regards
Ian


Jeff Mason
Senior Member



USA
621 Posts
 Posted - 08/31/2003 : 10:18:15 AM


--------------------------------------------------------------------------------

If your temporary table has a primary key column, then there is an alternative to copying the distinct rows into a temp table, deleting the source table then copying things back.

Let's assume you assigned an identity column as the primary key ('keycol' in my code below), and let's further assume you'd be happy to retain the first (lowest primary key value) row for any duplicates:


DELETE FROM YourTable
    WHERE keycol NOT IN
         (SELECT MIN(keycol)
            FROM YourTable
            GROUP BY Col1);



Jeff Mason
Custom Apps, Inc.
www.custom-apps.com

hai,

thanks for u r help. but this dosen't clarify my question. here u r supposed to delete records based on one column. But i need to delete records based on two column's.

regards

vamsi

Vamsi,

I had something similar, and so I used a cursor, I selected all the rows where c2 = A and c3 = B. I then looped through the retunred records and would delete all but the first row.

Does the make sense? In general cursors are not recommended but if you have a small number of similar rows and you don't plan on running this frequently it should be ok.

If you have any questions let me know.

Scott

if you want do get rid of duplicate records and don't care about which one you want to keep, here's what you can do
create a temp table and insert the distinct records into it
eg.
insert into #temp
select max(c1), c2, c3, max(c4) from [tableName]
group by c2, c3

then truncate the table
eg. truncate table [tableName]

Now insert the records from temp table back into the original table
eg.
insert into [tableName]
select * from #temp

Thanks for u r healp. I am able to get out of this problem.

vamsi


vamsi