Ygthkb

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;

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'

)

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.