Postgresql query deletes all rows

Question

I wrote a simple delete query in a PostgreSQL function with using clause, left join and a where clause. But the query does not take the where condition in consideration. It deletes all rows.

I wrote two types of query both produce same result

Query 1

delete from "StockInfos" using "StockInfos" as si
    left outer join "PurchaseOrderInfos" as poi on poi."Id" = si."PurchaseOrderInfoId"
    left outer join "ReceivingInfos" as ri on ri."PurchaseOrderInfoId" = poi."Id"
 where ri."Id" = (delete_data->>'Id')::bigint;

Query 2

delete from "StockInfos" where exists (
            select * from "StockInfos" as si
                left join "PurchaseOrderInfos" as poi on poi."Id" = si."PurchaseOrderInfoId"
                left outer join "ReceivingInfos" as ri on ri."PurchaseOrderInfoId" = poi."Id"
            where ri."Id" = (delete_data->>'Id')::bigint
        );

I don understand what is the problem. May anyone tell what is going wrong?

Unrelated to your problem, but: you should really avoid those dreaded quoted identifiers. They are much more trouble than they are worth it. wiki.postgresql.org/wiki/… — user330315
– user330315, Commented Jun 2, 2020 at 21:21

GMB · Accepted Answer · 2020-06-02 21:24:14Z

1

I would rephrase this with a correlated subquery. This makes the logic much cleaner, and should do what you want:

delete from "StockInfos" si
where exists (
    select 1
    from "PurchaseOrderInfos" poi
    inner join "ReceivingInfos" as ri on ri."PurchaseOrderInfoId" = poi."Id"
    where 
        oi."Id" = si."PurchaseOrderInfoId"
        and ri."Id" = (si.delete_data->>'Id')::bigint

)

answered Jun 2, 2020 at 21:24

GMB

224k25 gold badges103 silver badges151 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Ajmal-Hossain Over a year ago

ops! sorry delete_data is a parameter of the postgres function type of jsonb which contains an ReceivingInfoId which should match with the Id 's of "ReceivingInfos" table

Ajmal-Hossain Over a year ago

How ever this query works perfectly. Thanks a lot. what does select 1 means, may you please tell?

GMB Over a year ago

@Ajmal-Hossain: exists just cheks if the subquery returns something. Since we don't need the actual resultset, we use select 1. That's merely a convention (it could be select 0, select *, or select null...).

Collectives™ on Stack Overflow

Postgresql query deletes all rows

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related