UPDATE statement that doesn't make sense

Question

Post reply

UPDATE statement that doesn't make sense

yonision

Ten Centuries

Points: 1033
More actions
September 29, 2015 at 12:54 pm

#300998

Hi, I ran into an UPDATE statement in code that I never saw before, and strangly it works!
first, look at this one. This will fail because its not familiar with S1 in the subquery. makes sense to me
UPDATE s
SET s.StudentFirstName = ( SELECT 'lala'
FROM s1
)
FROM Students s ,
students1 s1
However! this one works somehow! can anybody tell me why, how and what does it mean?
UPDATE s
SET s.StudentFirstName = ( SELECT CASE WHEN 1 > 0
THEN s1.studentfirstname
END
)
FROM Students s ,
students1 s1

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Bill Talada SSChampion Points: 11954 More actions · Answer 1

I never use the old join syntax...and actually I don't see the join in your updates.

The first doesn't work because you are trying to select every row from s1.

The second works because it is getting the column from the current joined row.

Luis Cazares SSC Guru Points: 183706 More actions · Answer 2

The first one fails because you're trying to use the alias as a table. That's not how alias work, you can use them to qualify columns, but not in place of a table in a FROM.

That would be like having

SELECT *

FROM Students s

CROSS JOIN students1 AS s1

CROSS JOIN s1 AS s1again

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

yonision Ten Centuries Points: 1033 More actions · Answer 3

Thanks Bill.

and yet... what does it mean? I mean how can a substatement take from a table that's not in it? Lets simplify. this one is legal:

UPDATE s

SET s.StudentFirstName = ( SELECT s1.StudentFirstName

FROM dbo.Students x

WHERE x.StudentId = 1

)

FROM Students s ,

Students1 s1

the sub-select takes from s1, which is not in that statement. I know you can do it in a SELECT, then it would result in a correlated query. (a sub-SELECT reference the parent SELECT) But what would it mean in an UPDATE?

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

It's a correlated subquery. The fact that the outer query is an update doesn't change that, it's a subquery which references a table in the outer query, that's a correlated subquery.

It's also, as written, not particularly sensible, nor useful, since you have a cross join in the outer query and hence an update which is non-deterministic about which value gets updated to which row.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

yonision Ten Centuries Points: 1033 More actions · Answer 5

Thanks very much! (yes i know the SQL makes no sense... i just threw something together to emphasize the subquery part... the lack of JOIN indeed would be a problem in the real world)