SQL continues excecution when syntax error

%3CLINGO-SUB%20id%3D%22lingo-sub-84615%22%20slang%3D%22en-US%22%3ESQL%20continues%20excecution%20when%20syntax%20error%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-84615%22%20slang%3D%22en-US%22%3E%3CP%3EHi..%3CBR%20%2F%3EI%20have%20this%20statement%3A%3C%2FP%3E%3CP%3EUPDATE%20Table1%20SET%20status%20%3D%20'DoSomething'%3C%2FP%3E%3CP%3EWHERE%20status%20%3D%20'Ready'%20AND%20TabId%20IN%3CBR%20%2F%3E(SELECT%20TabId%20FROM%20Table2%20WHERE%20xData%20IS%20NOT%20NULL)%3CBR%20%2F%3E.%3CBR%20%2F%3EIf%20I%20run%20only%20the%20subselect%20I%20get%20an%20error%3A%3CBR%20%2F%3EInvalid%20column%20name%20%26nbsp%3B'TabId'%20-%20and%20that%20is%20correct%3A%20TabId%20is%20NOT%20on%20Table2%2C%20but%20only%20on%20Table1%3CBR%20%2F%3E.%3CBR%20%2F%3EBUT%3CBR%20%2F%3EWhen%20I%20run%20the%20entire%20statement%2C%20it%20updates%20all%20rows%20in%20Table1..%3C%2FP%3E%3CP%3EIt's%20ignoring%20the%20error%20in%20the%20subselect.%20Yes%20I%20know%20that%20it's%20because%20it's%20thinks%20that%20it%20is%20a%20reference%20out%20of%20the%20subselect.%3CBR%20%2F%3EBut%20how%20can%20it%20validate%20the%20%26nbsp%3B%20%26nbsp%3B%20IN%20()%20%26nbsp%3B%20%26nbsp%3B%20to%20be%20true%20%3F%3F%3F%3CBR%20%2F%3E%3CBR%20%2F%3E%2FClaudi%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-109597%22%20slang%3D%22en-US%22%3ERe%3A%20SQL%20continues%20excecution%20when%20syntax%20error%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-109597%22%20slang%3D%22en-US%22%3E%3CP%3EI%20recommend%20that%20you%20change%20the%20script%20as%20below%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EUPDATE%20Table1%20SET%20status%20%3D%20'DoSomething'%3C%2FP%3E%3CP%3EFROM%26nbsp%3B%3CSPAN%3ETable1%20INNER%20JOIN%26nbsp%3BTable2%20ON%26nbsp%3BTable1.TabId%20%3D%26nbsp%3BTable2.TabId%3C%2FSPAN%3E%3C%2FP%3E%3CP%3EWHERE%20%3CSPAN%3ETable1.%3C%2FSPAN%3Estatus%20%3D%20'Ready'%3C%2FP%3E%3CP%3EAND%20Table2.xData%20IS%20NOT%20NULL%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThis%20script%20can%20not%20only%20prevent%20the%20incident%20you%20have%20gotten%20but%20also%20validate%20the%20relationship%20between%20table1%20and%20table2%20before%20updating%20even%20though%20I%20know%20tabid%20only%20exists%20in%20table1....%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-93918%22%20slang%3D%22en-US%22%3ERe%3A%20SQL%20continues%20excecution%20when%20syntax%20error%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-93918%22%20slang%3D%22en-US%22%3E%3CP%3Eyou%20need%20to%20fully%20qualify%20the%20query%20to%20get%20the%20error%2C%20like%20below.%20%26nbsp%3B%20Because%20it's%20not%20fully%20qualified%2C%20it%20tries%20to%20auto-resolve%2C%20by%20referencing%20the%20outer%20query.%20%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EUPDATE%20Table1%20SET%20status%20%3D%20'DoSomething'%3CBR%20%2F%3EWHERE%20status%20%3D%20'Ready'%20AND%20TabId%20IN%3CBR%20%2F%3E(SELECT%20t2.TabId%20FROM%20Table2%20t2%20WHERE%20xData%20IS%20NOT%20NULL)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Visitor

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