PostgreSQL - Delete rows in table based on values in other table

Question

Attached SQLFiddle to this question.

i have two tables, i want to delete the inventoryfruitsforuser table with the fruitsForPrize table

it looks like this:

select * from fruitsForPrize where "prizeID" = 1
prizeID|fruitID
-------+-------
1      |1
1      |1
1      |1
1      |1
1      |2
1      |2
1      |3
1      |3
1      |4
1      |4
1      |4
1      |5
1      |5
1      |5

select * from inventoryfruitsforuser where "userID" = 1 
userID |fruitID
-------+-------
1      |1
1      |1
1      |1
1      |2
1      |2
1      |3
1      |4
1      |5
1      |5

now, userID 1 wants to get the prize 1 so there is a need to delete the prizeID 1 fruits from inventoryfruitsforuser

so it will be somthing like

(inventoryfruitsforuser where userID = 1) - (fruitsForPrize where prizeID = 1)

with that i'll get a table that looks like that:

inventoryfruitsforuser      
userID |fruitID
-------+-------
1      |1
1      |3
1      |4
1      |4
1      |5

I've done a query that checks if i have enough fruits to get the prize

with myfruits as (
    select "fruitID", count("fruitID") from inventoryFruitsForUser where "userID" = 1 group by "fruitID" order by "fruitID"
),fruitsRequired as (
    select "fruitID", count("fruitID") from fruitsForPrize where "prizeID" = 1 group by "fruitID" order by "fruitID"
),checkShouldDelete as (
    select fruitsRequired."fruitID" as "fruitsRequired.fruitID", fruitsRequired.count as "fruitsRequired.count", myfruits."fruitID" as "myfruits.fruitID", myfruits.count as "myfruits.count",
           (myfruits.count is not null and myfruits.count >= fruitsRequired.count) or (myfruits.count is null and fruitsRequired.count = 0) as "isEnough"
    from fruitsRequired
    left join myfruits
    on (fruitsRequired."fruitID" = myfruits."fruitID")
) SELECT bool_and("isEnough") "toDelete" FROM checkShouldDelete;

What I left to do is to delete rows from inventoryfruitsforuser where "userID" = 1 with the fruitsForPrize where "prizeID" = 1 so I'll get the third table up above.

thanks for helping!

Answer 1

Agree with @gwaigh that your schema could use a little work. In any case, one of the difficulties you have here is not having a primary key / unique key on each record, so deleting can get tough. This can be fixed easily enough (or you can go through the work of changing your model the way that @gwaigh describes - adding a "number_of_pieces" column):

ALTER TABLE fruitsForPrize ADD COLUMN id serial;
ALTER TABLE inventoryfruitsforuser ADD COLUMN id serial;

Once this in place, the delete becomes much more straightforward. I believe you are "removing the pieces from inventory and redeeming them for the prize", correct?

(I also think some of your column names could use some changing. Capitals get tough in SQL land)

The way I solved is by enumerating your fruit and then deleting the respective elements out of inventory.

I was not sure how quite to update the fiddle, so here is the delete query (which can obviously be added to your previous string of CTEs):

with prize_enum as (
     -- enumerate fruit for prize
    select "prizeID","fruitID"
    , id as prizefruitid
    , row_number() over (partition by "prizeID","fruitID" order by id) as fruitnum
    from fruitsForPrize
  )
, inv_enum as (
     -- enumerate the fruit in inventory
    select "userID","fruitID",id as invid
    , row_number() over (partition by "userID","fruitID" order by id) as fruitnum 
     from inventoryfruitsforuser
  )
DELETE FROM inventoryfruitsforuser z
USING
 (
 -- get the fruits we are redeeming for prize
  select *
  from prize_enum p
  join inv_enum i on i."fruitID" = p."fruitID"
    and i.fruitnum = p.fruitnum
) a
where a.invid = z.id
returning *;

select * from inventoryfruitsforuser;
-- returns the table you expect

(NOTE - does not address the possibility for multiple users or multiple prizes)

EDIT: The approach suggested by @gwaigh. I changed the DDL accordingly:

CREATE TABLE inventoryfruitsforuser ("userID" INTEGER NOT NULL, "fruitID" INTEGER NOT NULL, number_of_pieces integer NOT NULL DEFAULT 1);
CREATE TABLE fruitsForPrize ("prizeID" INTEGER NOT NULL, "fruitID" INTEGER NOT NULL, number_of_pieces integer NOT NULL DEFAULT 1);

INSERT INTO inventoryfruitsforuser ("userID", "fruitID",number_of_pieces)
VALUES  (1,1,4),
        (1,2,2),
        (1,3,2),
        (1,4,3), 
        (1,5,3);

INSERT INTO fruitsForPrize ("prizeID", "fruitID",number_of_pieces)
VALUES  (1,1,3),
        (1,2,2), 
        (1,3,1),
        (1,4,1),
        (1,5,2);

ALTER TABLE fruitsForPrize ADD COLUMN id serial;
ALTER TABLE inventoryfruitsforuser ADD COLUMN id serial;

The query for updating gets a bit easier, here. One thing to keep in mind is that I did not address multiple prizes (i.e. it would count the same fruit for multiple prizes because the inventory does not decrease until you are selecting the prize. This can be solved by looping over prizes, explicitly declaring a prize, or altering the query to be a bit more complex). Hopefully this illustrates the point, though:

with check_prize as (
  select p.*, u."userID", u.number_of_pieces >= p.number_of_pieces and u.number_of_pieces is not null as enough
  from fruitsForPrize p
  left join inventoryfruitsforuser u on u."fruitID" = p."fruitID"
  ) , enough_for_prize  as (
select "userID", "prizeID", true = ALL(array_agg(enough)) as enough
from check_prize
group by 1,2
    )
    update inventoryfruitsforuser u 
    set number_of_pieces = u.number_of_pieces - p.number_of_pieces
    from fruitsForPrize p
    join enough_for_prize e on e."prizeID" = p."prizeID" and e.enough
    where e."userID" = u."userID" and p."fruitID" = u."fruitID"
  returning *
    ;

select * from inventoryfruitsforuser;

If you were going to focus on a specific user / prize, I would probably wrap the whole thing in a stored procedure and use the user / prize as parameters... something like:

create or replace function user_redeem_prize(_user integer, _prize integer)
RETURNS BOOLEAN
AS $BODY$
BEGIN

    with check_prize as (
      select p.*, u."userID", u.number_of_pieces >= p.number_of_pieces and u.number_of_pieces is not null as enough
      from fruitsForPrize p
      left join inventoryfruitsforuser u on u."fruitID" = p."fruitID"
      where p."prizeID" = _prize and u."userID" = _user
      ) , enough_for_prize  as (
    select "userID", "prizeID", true = ALL(array_agg(enough)) as enough
    from check_prize
    group by 1,2
        )
        update inventoryfruitsforuser u 
        set number_of_pieces = u.number_of_pieces - p.number_of_pieces
        from fruitsForPrize p
        join enough_for_prize e on e."prizeID" = p."prizeID" and e.enough
        where e."userID" = u."userID" and p."fruitID" = u."fruitID"
        and e."userID" = _user
        and p."prizeID" = _prize
        ;

   RETURN TRUE;
END;
$BODY$
LANGUAGE PLPGSQL;

select user_redeem_prize(1,1);