Home

SQL continues excecution when syntax error

Claudi Magnussen
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
Highlighted

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

Related Conversations
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
cntvertex in Discussions on
13 Replies
Tabs and Dark Mode
cjc2112 in Discussions on
22 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies
description for autoplay blocking in settings page
HotCakeX in Discussions on
8 Replies