How Can I Use DISTINCT to Find Unique Combinations of Values Across Multiple Columns in SQL?

SQL: Identifying Unique Combinations Across Multiple Columns

This article addresses the challenge of finding unique combinations of values across multiple columns in SQL, specifically focusing on updating sales records marked as inactive. The goal is to identify sales that didn't occur on the same day at the same price and update their status to 'ACTIVE'. A simple DISTINCT clause won't suffice because it only works on single columns.

The GROUP BY clause offers a powerful solution:

SELECT a, b, c FROM t GROUP BY a, b, c;

This groups rows based on the unique combinations of columns a, b, and c, effectively achieving the same result as a multi-column DISTINCT (though GROUP BY provides more functionality).

For updating sales records, a nested query is effective:

UPDATE sales
SET status = 'ACTIVE'
WHERE id IN (
    SELECT id
    FROM sales S
    INNER JOIN (
        SELECT saleprice, saledate
        FROM sales
        GROUP BY saleprice, saledate
        HAVING COUNT(*) = 1
    ) T ON S.saleprice = T.saleprice AND S.saledate = T.saledate
);

This query uses a subquery to identify sales with unique saleprice and saledate combinations (where COUNT(*) = 1). The outer query then updates the status of these sales to 'ACTIVE'.

By using GROUP BY and nested queries, you can efficiently manage and update data based on unique combinations of values across multiple columns. This provides a robust and accurate method for handling complex data manipulation tasks.

The above is the detailed content of How Can I Use DISTINCT to Find Unique Combinations of Values Across Multiple Columns in SQL?. For more information, please follow other related articles on the PHP Chinese website!