SQL continues excecution when syntax error

Copper Contributor

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

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)

 

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....