Deleted whole table when the where clause is not executable statement

user_19194388 0
Viewed 30

I recently had an incident when run a delete statement and it deleted all records. I found the error but could not understand why it could be executed. I can simply demonstrate the incident like:

create table A(
aid int,
description varchar(50)
);

create table B(
bid int,
description varchar(50)
);

insert into A values(1, 'Row 1');
insert into A values(2, 'Row 2');
insert into A values(3, 'Row 3');

insert into B values(22, 'Row 1');
insert into B values(23, 'Row 2');
insert into B values(24, 'Row 3');

delete from A where aid in (select aid from B);

The delete statement deleted whole table A records, even column aid does not exists in table B. Why the subquery failed and the statement did not fail? I run it at PostgreSql and Oracle, all is the same result.

oracle11g postgresql sql
1 Answers

Since the table B has no aid column

delete from A where aid in (select aid from B);

is evaluated as

delete from A where aid in (select A.aid from B);

But you want

delete from A where A.aid in (select B.bid from B);

It's good programming practice to qualify all columns, at least when several tables are involved.

user_37060160
Related