By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
Help with an interesting Postgres question: Why isn't an Index Only Scan used on a partition accessed via the parent table?.
CREATE TABLE sales (
id serial
, saleprice numeric
, saledate timestamp
, status text
);
INSERT INTO sales (saleprice, saledate)
VALUES
(123, '2012-09-28 02:31')
, (123, '2012-09-28 02:31')
, (666, '2012-09-28 02:31') -- unique
, (123, '2012-09-29 06:36') -- unique
, (125, '2012-09-29 02:31')
, (125, '2012-09-29 02:31');
CREATE TABLE
INSERT 0 6
UPDATE sales s
SET status = 'ACTIVE'
WHERE NOT EXISTS (
SELECT 1
FROM sales s1
WHERE s1.saleprice = s.saleprice
AND s1.saledate = s.saledate -- no dupe
AND s1.id <> s.id -- except for row itself
);
UPDATE 2
TABLE sales;
| id | saleprice | saledate | status |
|---|---|---|---|
| 1 | 123 | 2012-09-28 02:31:00 | null |
| 2 | 123 | 2012-09-28 02:31:00 | null |
| 5 | 125 | 2012-09-29 02:31:00 | null |
| 6 | 125 | 2012-09-29 02:31:00 | null |
| 3 | 666 | 2012-09-28 02:31:00 | ACTIVE |
| 4 | 123 | 2012-09-29 06:36:00 | ACTIVE |
SELECT 6