Postgresql查询删除所有行。

Question

我在一个带有delete子句、left join和where子句的PostgreSQL函数中编写了一个简单的left join查询。但是查询没有考虑where条件。它删除所有行。

我编写了两种类型的查询，它们都产生相同的结果。

查询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;

查询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
        );

我不明白是什么问题。有人能告诉我出了什么问题吗？

Answer 1

我会用一个相关的子查询来重新表述这一点。这使得逻辑更加清晰，并且应该做您想做的事情：

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

)