0

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
                        ('[email protected]')
              )
        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
                        ('[email protected]')
              )
        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?

1 Answer 1

1

Your second query will fail, because d0 only has a column seller_id.

Your first query deletes all rows because the id in select id from d0 cannot refer to a column from d0, so it is taken as a column from the outer query, and the whole thing effectively becomes

WITH d0 AS (...)
DELETE FROM seller
WHERE seller.id IN (SELECT seller.id FROM d0)

and that condition will be true whenever d0 is not empty.

1
  • spell wrong for second sql. This seem like var scope in language. Can find in inner scope then the outer scope.
    – jiamo
    Commented Dec 3, 2020 at 5:27

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.