Forum Discussion
SQL continues excecution when syntax error
Hi..
I have this statement:
UPDATE Table1 SET status = 'DoSomething'
WHERE status = 'Ready' AND TabId IN
(SELECT TabId FROM Table2 WHERE xData IS NOT NULL)
.
If I run only the subselect I get an error:
Invalid column name 'TabId' - and that is correct: TabId is NOT on Table2, but only on Table1
.
BUT
When I run the entire statement, it updates all rows in Table1..
It's ignoring the error in the subselect. Yes I know that it's because it's thinks that it is a reference out of the subselect.
But how can it validate the IN () to be true ???
/Claudi
2 Replies
I recommend that you change the script as below:
UPDATE Table1 SET status = 'DoSomething'
FROM Table1 INNER JOIN Table2 ON Table1.TabId = Table2.TabId
WHERE Table1.status = 'Ready'
AND Table2.xData IS NOT NULL;
This script can not only prevent the incident you have gotten but also validate the relationship between table1 and table2 before updating even though I know tabid only exists in table1....
you need to fully qualify the query to get the error, like below. Because it's not fully qualified, it tries to auto-resolve, by referencing the outer query.
UPDATE Table1 SET status = 'DoSomething'
WHERE status = 'Ready' AND TabId IN
(SELECT t2.TabId FROM Table2 t2 WHERE xData IS NOT NULL)
