Update statement gives wrong result with subquery

Question

I have the following query which gives no error when I used a non-existent column reference in the subquery. The column which I referred in the subquery is actually a column in the table being updated.

create table tbl1 (f1 bigint, f2 char(10), f3 integer);
insert into tbl1 values (1, 'aa', 0);
insert into tbl1 values (2, 'bb', 0);
insert into tbl1 values (3, 'cc', 0);
insert into tbl1 values (4, 'dd', 0);

create table temp_tbl (ref_num bigint);
insert into temp_tbl values (1);
insert into temp_tbl values (3);

update tbl1 set f2='ok' where f1 in (select f1 from temp_tbl);
-- 4 records updated

can anyone tell me why it is not giving any error? and records are updated irrespective of the condition.

I tried this in both Oracle and SQLserver. results are same

Answer 1

This is happening because the values in a SELECT don't just have to be columns from the table you're selecting from, the sub-query is returning the value for f1 from the outer query, instead of a value from temp_tbl.

Consider if you re-wrote the UPDATE query to:

SELECT  *
FROM    tbl1 
WHERE   f1 IN (select f1 from temp_tbl);

The results returned would actually be:

When you're trying to reason about something like this (and as a generally good way of working to get queries right!), it's useful to write your UPDATE query in the form:

UPDATE  T
SET     F2 = 'ok'
FROM    TBL1 T
WHERE   T.f1 IN
        (
            SELECT  F1
            FROM    temp_tbl
        )

By writing it this way you can readily comment out the UPDATE and SET components of the query, replace them with a SELECT and see what the set of data the query will operate on is.

Answer 2

The sub-query's column reference goes to the outer table!

update tbl1 set f2='ok' where f1 in (select f1 from temp_tbl);

Is read as

update tbl1 set f2='ok' where f1 in (select tbl1.f1 from temp_tbl);

Qualify your columns:

update tbl1 set f2='ok' where f1 in (select temp_tbl.ref_num  from temp_tbl);