On Thursday, October 1, 2015, <jesper@udby.com
<javascript:_e(%7B%7D,'cvml','jesper@udby.com');>> wrote:
>
> -- Failure #1
> DELETE FROM uid_child WHERE parent_uid IN (SELECT parent_uid FROM
> uid_parent
> WHERE id=999);
> -- Gives (pgAdmin III): Query returned successfully: 0 rows affected, 21 ms
> execution time.
> -- psql: DELETE 0
> -- Should fail as there is no "parent_uid" in table uid_parent
>
> -- Failure #2
> DELETE FROM uid_child WHERE parent_uid IN (SELECT parent_uid FROM
> uid_parent
> WHERE id=1);
> -- Gives (pgAdmin III): Query returned successfully: 6 rows affected, 11 ms
> execution time.
> -- psql: DELETE 6
> -- Should fail - and this is rather important, as it actually deletes
> everything in uid_child as it is...
>
>
>
Not a bug. You made the subquery into a correlated subquery by referencing
a value in the containing part of the query. While I've now come to the
conclusion that this dynamic could be better treated in the documentation
it cannot be changed given the usefulness of such a construct. The
second best advice I can give is to prefix columns in subqueries with ther
source relation. The best advice is to make sure to test your destructive
queries before executing them.
David J.