473,320 Members | 2,071 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Query to delete one of the duplicate rows

Is there any query which will delete exactly one of the duplicate rows in a table and retain only one ?
Aug 22 '07 #1
4 2932
Saii
145 Expert 100+
Yes there are different ways to delete duplicate rows. What problem are you facing with your query?
Aug 23 '07 #2
amitpatel66
2,367 Expert 2GB
DELETE FROM <TABLE_NAME> WHERE ROWID NOT IN (SELECT MAX(ROWID) FROM <TABLE_NAME> GROUP BY <COLUMN_NAME> HAVING COUNT(COLUMN_NAME) >= 1)

Say you have following data:

Table emp_det

EMPID ENAME
101 A
101 A
102 B
102 B

Then the below query will delete the duplicate records from emp_det table.

DELETE FROM emp_det WHERE ROWID NOT IN (SELECT MAX(ROWID) FROM emp_det GROUP BY empid HAVING COUNT(empid) >= 1)
Aug 23 '07 #3
Thanks for ur reply.
i had the same problem.
Aug 27 '07 #4
hashim
1
hi
1-first u have to select the rowid for this rows
using flowing code:
select rowid from <table_name> where <condition >
2- delete from <table_name> where rowid=<choose the rowid from previous query>
3-commit
Sep 2 '07 #5

Sign in to post your reply or Sign up for a free account.

Similar topics

3
by: Alexander Anderson | last post by:
I have a DELETE statement that deletes duplicate data from a table. It takes a long time to execute, so I thought I'd seek advice here. The structure of the table is little funny. The following is...
11
by: suma | last post by:
Hello, I want to delete duplicate rows in a table when no primary key is defined. For eg: If we have table1 with data as below, Suma 23 100 Suma 23 100 I want to delete a row from this...
2
by: ms | last post by:
Access 2000: I am trying to delete duplicate records imported to a staging table leaving one of the duplicates to be imported into the live table. A unique record is based on a composite key of 3...
1
by: Dusty Reagan | last post by:
I'm trying to create a query that finds rows w/ duplicate "ContactKeys" then finds duplicate "AddressLine1s" out of the list of duplicate "ContactKeys." (I tried subqueries but it was really slow)...
6
by: Karl Richards | last post by:
I am attempting to delete duplicate rows in a spreadsheet using the Excel object. Does anyone have any idea how to do this? I've looked everywhere that I can find on the Web and have not been...
3
by: skennd | last post by:
Here's my problem in exact replication: I have used the find duplicate query in Access, and the query determined the following duplicate values by the following query: In (SELECT FROM As...
10
by: ranjitkumar | last post by:
Hi, Do anyone know a query for the following senario, I have a table in which i have multiple rows with the same entry. I want to keep only one copy of such rows. How to write a query to delete...
7
by: cobolman | last post by:
I have a table with the following columns, NAME, TYPE, TAG And there may be 'duplicates' on name and type. How can I delete them?? I want to delete all with duplicate NAME and TYPE
3
by: rajeshkrsingh | last post by:
Hi friends, Step1- create table duplicate ( intId int, varName varchar(50) ) insert into duplicate(intId,varName) values(1,'rajesh') insert into duplicate(intId,varName) values(2,'raj12')...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.