Delete duplicate rows in a ms sql table

delete from x where x.id in (select x.id from x group by x.id having count (*) > 1)

You can learn more in this link

http://support.microsoft.com/kb/139444

ms sql 2000

Of course you should use where I wrote  x.id the list of all the columns that are part of the key that will determine the duplication!

select tranno from tblcardtransactions where tblcardtransactions.id in (select tblcardtransactions.id from tblcardtransactions group by tblcardtransactions.id having count (*) > 1)



Server: Msg 207, Level 16, State 3, Line 1
Invalid column name 'id'.
Server: Msg 207, Level 16, State 1, Line 1
Invalid column name 'id'.
Server: Msg 207, Level 16, State 1, Line 1
Invalid column name 'id'.

tblcardtransactions.id should be replaced by the actual columns that being used for id.

If you need help with that please send me the table structure, you can use the following command:

sp_help  tblcardtransactions

tranDateTime      datetimeno      8                            no      (n/a)      (n/a)
storeNo      smallint      no      2      5          0          no      (n/a)      (n/a)
tranNo      smallint      no      2      5          0          no      (n/a)      (n/a)
tillNo      smallint      no      2      5          0          no      (n/a)      (n/a)
cashierNo      int      no      4      10         0          no      (n/a)      (n/a)
tranType      char      no      1                            no      no      no
cardNo      decimal      no      9      16         0          no      (n/a)      (n/a)
cardIssue      tinyint      no      1      3          0          no      (n/a)      (n/a)
itemCount      smallint      no      2      5          0          no      (n/a)      (n/a)
tranValue      money      no      8      19         4          no      (n/a)      (n/a)
SDValue      money      no      8      19         4          no      (n/a)      (n/a)

tblCardTransactions      dbo      user table      2003-02-28 16:42:35.170

What is your primary key column? do you want to delete all duplicate rows, or you want to retain one of the duplicate records?

What the mining of duplication in this table? can you show two duplicates records? all values should be the same in order to say it is duplication? if yes then:

select tranDateTime, storeNo, tranNo, tillNo, cashierNo, tranType, cardNo, cardIssue, itemCount, tranValue,SDValue
into tempTable
from tblcardtransactions
group by tranDateTime, storeNo, tranNo, tillNo, cashierNo, tranType, cardNo, cardIssue, itemCount, tranValue,SDValue
having count (*) = 1

delete from tblcardtransactions

insert into tblcardtransactions
select * from tempTable

commit

is there any other methods other than above using a single query without creating a another table ?

Yes, creating id column (auto number) that will be used as a key

ALTER TABLE tblcardtransactions
ADD id INTEGER AUTONUMBER;

then using the following:

delete from tblcardtransactions where tblcardtransactions.id in
(select max(id)
from tblcardtransactions
group by tranDateTime, storeNo, tranNo, tillNo, cashierNo, tranType, cardNo, cardIssue, itemCount, tranValue,SDValue
 having count (*) > 1)

is there any method without altering the table ?

can we using primary key of that table ?

Check here also , i have posted another question related to this , i got the query ..to do this using cursor ..please see

http://www.experts-exchange.com/Programming/Languages/SQL_Syntax/Q_24035258.html

What is the primary key?

...transdatetime

i dont know its primary key but it should be a unique key ..

I don't understand, what do you consider duplication?
Currently transdatetime is unique or not?

As long that the duplicated records are identical in all their columns then no other option.

The curser in your other question is an oracle cursor and uses rownum condition that does not exists in sql server. There is no way to delete one of the records since we don't have uniquness to use in the condition.