-
Hi,
Is there a way of deleting duplicate records without using the rowId column.
Actually I want to delete duplicates in a sybase database.
Thanks
Ronnie
ronnie_yours@yahoo.com
You can if you think you can.
-
Assuming you have a PK, count by your PK and delete the min() or max().
Jeff Hunter
-
Originally posted by marist89
Assuming you have a PK, count by your PK and delete the min() or max().
Nope there is no primary key.
Ronnie
ronnie_yours@yahoo.com
You can if you think you can.
-
Originally posted by ronnie
Nope there is no primary key.
How do you know you have duplicates, then?
Jeff Hunter
-
Originally posted by marist89
Originally posted by ronnie
Nope there is no primary key.
How do you know you have duplicates, then?
select account_id, trade_date, settlement_date, amount, quantity
from trades
group by account_id, trade_date, settlement_date, amount, quantity
having count(*) > 1
Ronnie
ronnie_yours@yahoo.com
You can if you think you can.
-
Oh, so you do have a PK.
Using the exact query you specified, I would create a temporary table of all my unique combinations. Then, I would figure out which row I want to drop with a min/max function and put that in the temporary table too. Once I knew the rows I wanted to delete, I would delete them from my base table.
Jeff Hunter
-
Originally posted by marist89
Oh, so you do have a PK.
Using the exact query you specified, I would create a temporary table of all my unique combinations. Then, I would figure out which row I want to drop with a min/max function and put that in the temporary table too. Once I knew the rows I wanted to delete, I would delete them from my base table.
Jeff,
i dont have a PK on the table at all. It can have multiple records with the same account Id .
Can it be done using one query/delete statement
Thanks
Ronnie
ronnie_yours@yahoo.com
You can if you think you can.
-
could you not create a table as select distinct (*) from original table and then either
a) drop the original table and rename the new one (is this possible)?
or
b) truncate the original tables and populate it from the new table.
-
A table without PK is not a table at all.
-
Originally posted by tamilselvan
A table without PK is not a table at all.
what if those are the requirements and you have to delete the data only after certain checks and balances are taken care of. :-)
Ronnie
ronnie_yours@yahoo.com
You can if you think you can.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|