How do I (or can I) SELECT DISTINCT on multiple columns?

by 
SELECT DISTINCT a,b,c FROM t

is roughly equivalent to: 

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

It’s a good idea to get used to the GROUP BY syntax, as it’s more powerful.

For your query, I’d do it like this:

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
 )

More Related Contents:

  1. How to delete duplicate rows without unique identifier
  2. updating table rows in postgres using subquery
  3. Return pre-UPDATE column values using SQL only
  4. Casting NULL type when updating multiple rows
  5. Slow simple update query on PostgreSQL database with 3 million rows
  6. SQL update fields of one table from fields of another one
  7. Bulk/batch update/upsert in PostgreSQL
  8. Postgres error updating column data
  9. Update a column of a table with a column of another table in PostgreSQL
  10. Functions with variable number of input parameters
  11. How to select records without duplicate on just one field in SQL?
  12. How to bulk insert only new rows in PostreSQL
  13. How can I do an UPDATE statement with JOIN in SQL Server?
  14. Auto increment table column
  15. How to list table foreign keys
  16. Two SQL LEFT JOINS produce incorrect result
  17. Postgresql GROUP_CONCAT equivalent?
  18. What is the maximum number of columns in a PostgreSQL select query
  19. Humanized or natural number sorting of mixed word-and-number strings
  20. Pass multiple values in single parameter
  21. Custom SERIAL / autoincrement per group of values
  22. PostgreSQL: encoding problems on Windows when using psql command line utility
  23. Eliminating duplicate values based on only one column of the table
  24. Aggregate a single column in query with many columns
  25. Aggregating (x,y) coordinate point clouds in PostgreSQL
  26. Does Postgres support nested or autonomous transactions?
  27. PostgreSQL delete with inner join
  28. Why is iterating through a large Django QuerySet consuming massive amounts of memory?
  29. Check if NULL exists in Postgres array
  30. Return rows from INSERT with ON CONFLICT without needing to update

Leave a Comment