CODEWITHSUNDEEP
sqlsql-serversql-server-2008
Jill Clover
Jill Clover

Reputation: 2328

Update from CTE issue

I am updating from a cte but got this common error The multi-part identifier "t.newfips" could not be bound. I know how to handle this error in other cases, but I don't know how in this case.

My query

with t as (
select * from FIPSModificationsBEA t1
inner join [BEAtoFIPS] t2
on cast(t1.newfips as varchar) = concat(t2.statefips,t2.CountyFIPS)
)
update [BEAtoFIPS]
set statefips = left(t.oldfips, 2) , countyfips = right(t.oldfips, 3)
from [BEAtoFIPS] t2
where concat(t2.statefips, t2.countyfips) =  t.newfips
go

I checked the query by the following query

with t as (
select * from FIPSModificationsBEA t1
inner join [BEAtoFIPS] t2
on cast(t1.newfips as varchar) = concat(t2.statefips,t2.CountyFIPS)
)
select newfips from t
go

This query works. Thus, I am confused. Why the latter one works but the former one does not? Aren't they essentially the same in structure? What's wrong with the former one?

Upvotes: 0

Views: 44

Answers (3)

Alex
Alex

Reputation: 276

You haven't joined your CTE in your update query so t isn't assigned.

Try this:

WITH tempData AS (
    SELECT * FROM FIPSModificationsBEA t1
    INNER JOIN [BEAtoFIPS] t2
    ON CAST(t1.newfips as varchar) = CONCAT(t2.statefips,t2.CountyFIPS)
)

UPDATE t2
SET statefips = LEFT(t.oldfips, 2), 
    countyfips = right(t.oldfips, 3)
FROM [BEAtoFIPS] AS t2
INNER JOIN tempData AS t
    ON t.newFips = concat(t2.statefips, t2.countyfips)

However you should be able to do this without the cte at all:

UPDATE b
SET statefips = LEFT(m.oldfips, 2), 
countyfips = RIGHT(m.oldfips, 3)
FROM [BEAtoFIPS] AS b
INNER JOIN FIPSModificationsBEA AS m
    ON CAST(m.newfips as varchar) = CONCAT(b.statefips,b.CountyFIPS)

Upvotes: 1

IMSoP
IMSoP

Reputation: 97938

Your UPDATE query doesn't have a FROM entry for the CTE t (but your SELECT does, which is why it works).

One way to think of CTEs is like a temporary view visible only to one query. So what you have is (sort of) like this:

Create View t (...) 
as 
(
select * from FIPSModificationsBEA t1
inner join [BEAtoFIPS] t2
on cast(t1.newfips as varchar) = concat(t2.statefips,t2.CountyFIPS)
)
go

update [BEAtoFIPS]
set statefips = left(t.oldfips, 2) , countyfips = right(t.oldfips, 3)
from [BEAtoFIPS] t2
where concat(t2.statefips, t2.countyfips) =  t.newfips
go

It's probably more obvious in this example that t is not actually defined anywhere in the UPDATE query.

To fix it, you need to JOIN to the view to tell it how it's connected to the query:

update [BEAtoFIPS]
set statefips = left(t.oldfips, 2) , countyfips = right(t.oldfips, 3)
from [BEAtoFIPS] t2
join t
on concat(t2.statefips, t2.countyfips) =  t.newfips
go

The query with the CTE needs to look exactly the same - JOIN to the CTE and specify how it relates to the rest of the query.

Upvotes: 1

SqlZim
SqlZim

Reputation: 38063

You can just update the cte:

with t as (
select * from FIPSModificationsBEA t1
inner join [BEAtoFIPS] t2
on cast(t1.newfips as varchar) = concat(t2.statefips,t2.CountyFIPS)
)
update t
set statefips = left(t.oldfips, 2) , countyfips = right(t.oldfips, 3)
go

Otherwise join to the cte

with t as (
select * from FIPSModificationsBEA t1
inner join [BEAtoFIPS] t2
on cast(t1.newfips as varchar) = concat(t2.statefips,t2.CountyFIPS)
)
update t2
set statefips = left(t.oldfips, 2) , countyfips = right(t.oldfips, 3)
from [BEAtoFIPS] t2
  inner join t
    on concat(t2.statefips, t2.countyfips) =  t.newfips
go

Upvotes: 1

Related Questions