Forum

Forums
SQL
Interview Questions...
How to Write an SQL...
 
Share:
Notifications
Clear all

How to Write an SQL Query to Delete Duplicate Rows While Keeping One Copy?

 
    Last Post
RSS

Posts: 71
 Vinay Kumar
Guest
Topic starter
Nov 09, 2024 6:26 am
(@Vinay Kumar)
Trusted Member
Joined: 4 years ago

How can you delete duplicate rows in an SQL table while keeping only one copy of each? Assume you have a products table with columns product_id, product_name, and price, where there may be duplicate entries for product_name and price.

Answer:

Removing duplicates while keeping one copy can be achieved by using the ROW_NUMBER() window function, which assigns a unique number to duplicate rows based on a defined order. Here’s a step-by-step solution using this approach.

Solution: Using CTE and ROW_NUMBER()

Assume the products table structure is as follows:

product_id product_name price
1 Laptop 1200
2 Laptop 1200
3 Phone 800
4 Phone 800
5 Tablet 500

In this table, we want to remove duplicates of product_name and price, keeping only one copy of each unique combination.

SQL Query:

sql
 
WITH duplicates AS (
SELECT product_id, product_name, price,
ROW_NUMBER() OVER (PARTITION BY product_name, price ORDER BY product_id) AS row_num
FROM products
)
DELETE FROM products
WHERE product_id IN (
SELECT product_id
FROM duplicates
WHERE row_num > 1
);

Explanation:

  1. Common Table Expression (CTE): We use a WITH clause to define a CTE named duplicates, where we assign row numbers to each duplicate row based on product_name and price.
    • PARTITION BY product_name, price: This divides rows into groups based on product_name and price values.
    • ROW_NUMBER() OVER (...): For each group, ROW_NUMBER assigns a unique number starting from 1.
  2. Filtering Duplicates: In the main DELETE query, we delete all rows with a row_num greater than 1, effectively keeping only the first occurrence of each duplicate.
  3. Ensuring One Copy Remains: The row_num = 1 row remains for each unique combination, so we retain one copy of each product.

Expected Output:

After running this query, duplicates will be removed, leaving only one unique entry for each combination of product_name and price.

product_id product_name price
1 Laptop 1200
3 Phone 800
5 Tablet 500

Leave a reply

Author Name

Author Email

Title *

 
Preview 0 Revisions Saved
Forum Jump:
  Previous Topic
Next Topic  
Share: