I was working on a trouble ticket. Traced the problem down to some SQL code that was actually in a Pro*C file. Here was the update statement:
UPDATE table1 one
SET dummy=200
WHERE table1.ROWID IN
(
SELECT innerone.ROWID
FROM table1 innerone,
table2 two
WHERE innerone.id = two.id
);
Now I wanted to limit the subquery to only choose those rows that had the code column of table1 equal to 5. Therefore I added another criteria to the inner query WHERE clause. My natural instinct was to use the alias from the inner query itself like this:
UPDATE table1 one
SET dummy=200
WHERE table1.ROWID IN
(
SELECT innerone.ROWID
FROM table1 innerone,
table2 two
WHERE innerone.id = two.id
AND innerone.code = 5
);
My reasoning was that it would be faster to reference just the inner subquery tables. I did not want to reference the outer query table, as that would make it a correlated subquery. However another developer came in behind me and changed my code to reference the outer query table alias:
UPDATE table1 one
SET dummy=200
WHERE table1.ROWID IN
(
SELECT innerone.ROWID
FROM table1 innerone,
table2 two
WHERE innerone.id = two.id
AND one.code = 5
);
Shouldn't these both equate to the same results? This other developer is out of the office now. But I am hoping to get an explanation when he gets back. What do you think?
