Menu
To find the duplicate Names in the table, we have to follow these steps: Defining the criteria: At first, you need to define the criteria for finding the duplicate Names. You might want to search in a single column or more than that. Write the query: Then simply write the query to find the duplicate Names.
update test_dup set done = 'error' where (acc_num,tel_num, imsi) in (select acc_num, tel_num, imsi from test_dup group by acc_num, tel_num, imsi having count(acc_num) > 1); Then it updates 5 rows i.e. all duplicate rows except non-dups.
Aggregate the column by COUNT, then use a HAVING clause to find values that appear greater than one time.
SELECT column_name, COUNT(column_name)
FROM table_name
GROUP BY column_name
HAVING COUNT(column_name) > 1;
Another way:
SELECT *
FROM TABLE A
WHERE EXISTS (
SELECT 1 FROM TABLE
WHERE COLUMN_NAME = A.COLUMN_NAME
AND ROWID < A.ROWID
)
Works fine (quick enough) when there is index on column_name. And it's better way to delete or update duplicate rows.
Simplest I can think of:
select job_number, count(*)
from jobs
group by job_number
having count(*) > 1;
You don't need to even have the count in the returned columns if you don't need to know the actual number of duplicates. e.g.
SELECT column_name
FROM table
GROUP BY column_name
HAVING COUNT(*) > 1
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With