Delete records if not present in subquery

I want to delete records from a table that are not found in a subquery. But it's not having the desired result.

The table has multiple rows per SKU. Each price row has a valid_from and valid_to date fields. I want to erase all prices but keep those that are currently valid (where valid_to is greater or equal to current date). I want to maintain future prices (where valid_from is in the future), but one step at a time.

Here's what I tried:

delete from prices
    where prices.id <> (
        select distinct on (sku) prices.id
        from prices p2
            where
                p2.valid_to >= CURRENT_DATE
            and p2."type" = 'regular'
            and prices.id = p2.id
        order by sku, p2.valid_from desc, p2.inserted_at desc
    )

The table has around 500k rows. This subquery returns around 23k rows. I expect that my above query will delete everything but those 23k rows.

select distinct on (sku) prices.id
    from prices
        where
            valid_to >= CURRENT_DATE
        and "type" = 'regular'
    order by sku, valid_from desc, inserted_at desc

But it's not deleting anything. Why does it not work the way I expect it to?

```
CREATE TABLE "public"."prices" (
"id" Bigint DEFAULT nextval('prices_id_seq'::regclass) NOT NULL,
"type" Character Varying( 255 ) NOT NULL,
"unit_price" Numeric( 11, 3 ) NOT NULL,
"wholesale_price" Numeric( 11, 3 ),
"min_wholesale_quantity" Numeric( 11, 3 ),
"valid_from" Date NOT NULL,
"valid_to" Date NOT NULL,
"product_cost" Numeric( 11, 3 ) NOT NULL,
"tax_rate" Numeric( 11, 3 ) NOT NULL,
"store_product_id" Bigint,
"inserted_at" Timestamp Without Time Zone NOT NULL,
"updated_at" Timestamp Without Time Zone NOT NULL,
"sku" Character Varying( 2044 ),
PRIMARY KEY ( "id" ) );

CREATE INDEX "prices_store_product_id_index" ON "public"."prices" USING btree( "store_product_id" Asc NULLS Last );

CREATE INDEX "index_sku" ON "public"."prices" USIN

Use the "not in", and don't refer to the outer query:

delete from prices
where (sku,id) Not in (
    select p2.sku, p2.id
    from prices p2
        where p2.valid_to >= CURRENT_DATE
        and p2."type" = 'regular'
)

StackExchange Database Administrators Q#211481, answer score: 3