Update Column WHEN table1.field_name = table2.field_name WITH table2.field_name_2

Question

Post reply

Update Column WHEN table1.field_name = table2.field_name WITH table2.field_name_2

Triality

SSCommitted

Points: 1958
More actions
July 6, 2011 at 1:48 pm

#389911

Hello -
I am trying to update a column in my table using the following code:
UPDATE dbo.MasterStage
SET Client =
CASE
WHEN [Carrier] IN (SELECT DISTINCT(C.[Carrier]) FROM dbo.Crosswalk as C)
THEN database_Name.DBO.Crosswalk.[Client]
ELSE 'OTHER'
END
I am getting an error though that says:
Msg 4104, Level 16, State 1, Line 5
The multi-part identifier "database_Name.DBO.Crosswalk.[Client]" could not be bound.
Any ideas why this won't work and how I can modify it to make it work?
Thanks!

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

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

LutzM SSC Guru Points: 107049 More actions · Answer 1

What are you trying to do?

It seems like you're looking for a join. Maybe something like this?

--UPDATE dbo.MasterStage

--SET Client =

SELECT CASE

WHEN dbo.MasterStage.[Carrier] IN (SELECT DISTINCT(C.[Carrier]) FROM dbo.Crosswalk as C)

THEN crw.[Client]

ELSE 'OTHER'

END

FROM dbo.MasterStage

LEFT OUTER JOIN database_Name.DBO.Crosswalk crw on dbo.MasterStage.Client = crw.[Client]

Side note: I change the UPDATE into a SELECT since I don't know if the result is what you're looking for....

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

Triality SSCommitted Points: 1958 More actions · Answer 2

Its a data warehouse environment and I want to lay the Client name into the Client field of the table using the crosswalk.

I want to match the table1.Carrier with table2.Carrier and put the table2.Client into the table1.client field.

Bruce W Cassidy SSChampion Points: 12668 More actions · Answer 3

See Craig's answer below (my brain just wasn't working on this one.)

Evil Kraig F SSC Guru Points: 100851 More actions · Answer 4

Triality (7/6/2011)
Its a data warehouse environment and I want to lay the Client name into the Client field of the table using the crosswalk.
I want to match the table1.Carrier with table2.Carrier and put the table2.Client into the table1.client field.

Erm, you're using MSSQL, right?

database_Name.DBO.Crosswalk.[Client]

is an invalid choice in the way you used it. The reason is you can't just go to a table and request a column, you have to tell it which row you want.

so, you're Updating MasterStage. If the Carrier exists in the dbo.Crosswalk table, then do something.

That something is merely (database_Name.DBO.Crosswalk.[Client]). This means nothing to the compiler. What you're probably looking for is to replace that portion (between the THEN and the ELSE) with, including the parentheses:

(SELECT TOP 1 Client FROM dbo.Crosswalk AS c WHERE Carrier = c.Carrier)

- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA

Fraggle-805517 SSCrazy Eights Points: 8907 More actions · Answer 5

Triality (7/6/2011)
Hello -
I am trying to update a column in my table using the following code:
UPDATE dbo.MasterStage
SET Client =
CASE
WHEN [Carrier] IN (SELECT DISTINCT(C.[Carrier]) FROM dbo.Crosswalk as C)
THEN database_Name.DBO.Crosswalk.[Client]
ELSE 'OTHER'
END
I am getting an error though that says:
Msg 4104, Level 16, State 1, Line 5
The multi-part identifier "database_Name.DBO.Crosswalk.[Client]" could not be bound.
Any ideas why this won't work and how I can modify it to make it work?
Thanks!

First, the reason that "database_Name.DBO.Crosswalk.[Client]" doesn't work is that SQL is treating it as fully qualified name. However, the way the statement is structures is not correct. 4 part naming is for tables, not for an individual column. [ServerName].[DatabaseName].[Schema].[Table] is how SQL Server is trying to read this. So in your example, it is looking for a Server name in sys.servers called "database_name", and it can't find it, which is why are getting the error.

In regards to your query, might the following work, or some other type of variation?

UPDATE MS

SET Client = CASE WHEN C.Carrier IS NULL THEN 'Other' ELSE Client

FROM MasterStage as MS

LEFT JOIN (

SELECT DISTINCT C1.Carrier, C1.Client

FROM Crosswalk AS C1

) AS C ON MS.Carrier = C.Carrier

Just need to be careful here as I am making an assumption that we have a 1-to-1 relationship between Carrier and Client. If that isn't the case, the query above will probably return bad data. If the query above doesn't work, maybe a sample of data and such would help us understand what you are trying to accomplish.

Thanks,

Fraggle.

Triality SSCommitted Points: 1958 More actions · Answer 6

That you Craig and Fraggle! Your insight was very valuable to me!

Here is how I made this work:

UPDATE C

SET C.Client = D.[Client] FROM dbo.Client_Stage_Test C LEFT OUTER JOIN dbo.Crosswalk D ON C.[Carrier] = D.[Carrier]

UPDATE dbo.Client_Stage_Test

SET Client = 'OTHER' WHERE Client IS NULL