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 &lt;&gt; (
        select distinct on (sku) prices.id
        from prices p2
            where
                p2.valid_to &gt;= 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 &gt;= 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;
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 &gt;= CURRENT_DATE
        and p2."type" = 'regular'
)