Okay, so a little bit ago I needed to join some tables on a varchar field for which some values start with an ID number and some don't (and before you ask, no, we didn't design this, this gets pulled from client data files). I need to apply a mapping between rows in the tables where this field starts with the same ID, regardless of what else is in the string. Like a good little data monkey, I run a select statement first to make sure my joins are correct and so forth.
Here's a portion of this statement, slightly anonymized:
select *
from Item i
inner join Glossary g on i.GlossID = g.GlossID
inner join LastYearItem lyi
on
case
when isnumeric(left(g.TransText,1))=1
...
It goes on from there, but that's the important part. Anyway, I run this statement and it does exactly what I want. Now I just change the first line to turn it into an update statement...
update lyi set lyi.NewItemID = i.ItemID
from Item i
inner join Glossary g on i.GlossID = g.GlossID
inner join LastYearItem lyi
on
case
when isnumeric(left(g.TransText,1))=1
...
Here SQL Server refuses and throws an exception: there's an invalid cast.
Wait, what? All of the casts were run in the joins, which are completely unchanged from the previous statement. Why is it now broken?
After looking at the values it was complaining about, I realized something unusual: It was now running through every entry in Glossary. So, just for the sake of trying something out, I add this:
update lyi set lyi.NewItemID = i.ItemID
from Item i
inner join Glossary g on i.GlossID = g.GlossID
inner join LastYearItem lyi
on
case
when isnumeric(left(g.TransText,1))=1 <font color="red">and i.GlossID = g.GlossID</font>
...
This works absolutely fine.
I'm not entirely sure how SQL Server implements inner joins, or why it does so differently between select and update statements, but I guess that'll teach me for assuming i.GlossID is equal to g.GlossID, especially after inner-joining on i.GlossID being equal to g.GlossID.