There are several ways to find duplicate values in a table in Oracle:
1. Using the GROUP BY clause and the HAVING clause:
SELECT column1, column2, ..., COUNT(*)
FROM table_name
GROUP BY column1, column2, ...
HAVING COUNT(*) > 1;
This query will return all rows where the value of the specified columns is duplicated.
2. Using the DISTINCT keyword:
SELECT column1, column2, ...
FROM table_name
GROUP BY column1, column2, ...
HAVING COUNT(DISTINCT column1, column2, ...) < COUNT(*)
This query will return all rows where the values of the specified columns are not unique.
3. Using the EXISTS clause:
SELECT column1, column2, ...
FROM table_name t1
WHERE EXISTS (SELECT 1
FROM table_name t2
WHERE t1.column1 = t2.column1
AND t1.column2 = t2.column2
AND ...
AND t1.rowid != t2.rowid);
This query will return all rows where the values of the specified columns are duplicated and are not the same row.
4. Using ROW_NUMBER() function
SELECT column1, column2, ...
FROM (
SELECT column1, column2, ...,
ROW_NUMBER() OVER (PARTITION BY column1, column2, ... ORDER BY column1) as rn
FROM table_name
)
WHERE rn > 1;
This query will return all rows where the values of the specified columns are duplicated.
In all of the above examples, you should replace "column1", "column2", etc., with the actual names of the columns you want to check for duplicates and "table_name" with the real name of the table. It is important to note that when checking for duplicates, you should include all columns that are part of the primary key or unique constraint in the SELECT statement.
0 Komentar