Both these two sql can be execute , the first one delete all , the second one is logic right.
the first one
with d0 as (
delete from seller_create_request
where seller_id in
(
select id
from seller
where email in
('xx@xx.com')
)
returning seller_create_request.seller_id
)
delete from seller
where id in (select id from d0)
returning seller.id;
the second one
with d0 as (
delete from seller_create_request
where seller_id in
(
select id
from seller
where email in
('xx@xx.com')
)
returning seller_create_request.seller_id
)
delete from seller
where id in (select d0.seller_id from d0)
returning seller.id
;
The first one select id from d0
it seem equal to select id from seller
.
My original intention is select seller_id from d0
but have spell wrong. It don't cause
column id does not exist
since it from d0.
So I am strange why psql has this behavior, what I am missing from simple understanding?
Best Answer
Your second query will fail, because
d0
only has a columnseller_id
.Your first query deletes all rows because the
id
inselect id from d0
cannot refer to a column fromd0
, so it is taken as a column from the outer query, and the whole thing effectively becomesand that condition will be true whenever
d0
is not empty.