Write a DELETE statement to remove duplicate records from a product table, keeping only one record for product ID 101.

You can delete duplicates by using a common table expression (CTE) with ROW_NUMBER to identify duplicates. Example:

WITH ranked AS (
  SELECT *,
         ROW_NUMBER() OVER (PARTITION BY product_id ORDER BY id) AS rn
  FROM product
  WHERE product_id = 101
)
DELETE FROM product
WHERE id IN (
  SELECT id FROM ranked WHERE rn > 1
);

This assigns a row number to each record within the same product_id, ordering by the primary key id. All rows with rn > 1 are duplicates and are deleted, leaving only the first row (rn = 1). Complexity is O(n) for the partition, where n is the number of rows for product 101. The statement is safe to run on large tables because it uses a set-based operation rather than row-by-row loops.