I need to retrieve all rows from a table where 2 columns combined are all different. So I want all the sales that do not have any other sales that happened on the same day for the same price. The sales that are unique based on day and price will get updated to an active status.

This query, while correct and being accepted for year now, is extremely inefficient and unnecessarily so. Don't use this. I provided an alternative and some explanation in another answer.
–
Erwin BrandstetterSep 30 '12 at 20:45

Which is much faster than either of them. Nukes the performance of the currently accepted answer by factor 10 - 15 (in my tests on PostgreSQL 8.4 and 9.1).

But this is still far from optimal. Use a NOT EXISTS (anti-)semi-join for even better performance. EXISTS is standard SQL, has been around forever (at least since PostgreSQL 7.2, long before this question was asked) and fits the presented requirements perfectly:

Unique key to identify row

Every table should have a primary key. Add one if you didn't have one, yet. I suggest a serial.

How is this faster?

The subquery in the EXISTS (anti-)semi-join can stop evaluating as soon as the first dupe is found (no point in looking further). For a base table with few duplicates this is only mildly more efficient. With lots of duplicates this becomes way more efficient.
Also, IN is generally slow for bigger sets in PostgreSQL.

Exclude empty updates

If some or many rows already have status = 'ACTIVE', your update would not change anything, but still insert a new row version at full cost (minor exceptions apply). Normally, you do not want this. Add another WHERE condition like demonstrated above to make this even faster:

Good answer. I'm a sql server guy, so the first suggestion of using a tuple with an IN ( ) check wouldn't occur to me. The not exists suggestion is usually gonna end up with the same execution plan in sql server as the inner join.
–
Joel CoehoornSep 30 '12 at 21:03

Nice. The explanation greatly increases the value of the answer. I'm almost tempted to run some tests with Oracle to see how the plans compare with Postgres and SQLServer.
–
PeterAug 15 '13 at 14:59

The problem with your query is that when using a GROUP BY clause (which you essentially do by using distinct) you can only use columns that you group by or aggregate functions. You cannot use the column id because there are potentially different values. In your case there is always only one value because of the HAVING clause, but most RDBMS are not smart enough to recognize that.

This should work however (and doesn't need a join):

UPDATE sales
SET status='ACTIVE'
WHERE id IN (
SELECT MIN(id) FROM sales
GROUP BY saleprice, saledate
HAVING COUNT(id) = 1
)

You could also use MAX or AVG instead of MIN, it is only important to use a function that returns the value of the column if there is only one matching row.