Delete records if not present in subquery

Question

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" USING btree( "sku" Asc NULLS Last );

ALTER TABLE "public"."prices"
    ADD CONSTRAINT "prices_store_product_id_fkey" FOREIGN KEY ( "store_product_id" )
    REFERENCES "public"."store_products" ( "id" ) MATCH SIMPLE
    ON DELETE Cascade
    ON UPDATE No Action;

Answer 1

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'
)

Answer 2

Based on your comment "I want to erase all prices and only leave those currently valid. I know my query will also erase future prices (those where valid_from is in the future), but one step at a time", here are two steps, erase only old prices:

delete from prices
  where p2.valid_to < CURRENT_DATE
     or p2."type" != 'regular'

If this is too much for a single transaction, add a test on the number of rows (limit, rownum < ...) and repeat until all old prices are gone.