1

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.

rcdnz27
  • 11
  • 1

1 Answers1

3

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.

jarlh
  • 42,561
  • 8
  • 45
  • 63
  • Good answer. I cannot commence with the logic, but it is good to know it. Thanks. A small mistake let me pay a big cost. – rcdnz27 May 25 '22 at 09:55