The multi-part identifier could not be bound.

Question

Post reply

The multi-part identifier could not be bound.

rgtft

Hall of Fame

Points: 3518
More actions
May 7, 2008 at 7:04 am

#95925

I'm trying to do an UPDATE statement where I join two tables. I want to update fields in table A with values from table B where the claim number matches in both tables. I get the error: The multi-part identifier "dbo.tblPharmacyClaimsTemp.LastName" could not be bound. What is this error telling me?
UPDATE dbo.tblClmsRx
SET dbo.tblClmsRx.RxPrescriberLName = LTRIM(RTRIM(dbo.tblPharmacyClaimsTemp.LastName))
FROM dbo.tblClmsRx
INNER JOIN dbo.tblPharmacyClaimTemp ON (dbo.tblClmsRx.RxClaimNum = dbo.tblPharmacyClaimTemp.RxClaimNum)
My code looks similar (to me) as the example code in books online
IF OBJECT_ID ('dbo.Table1', 'U') IS NOT NULL
DROP TABLE dbo.Table1;
GO
IF OBJECT_ID ('dbo.Table2', 'U') IS NOT NULL
DROP TABLE dbo.Table2;
GO
CREATE TABLE dbo.Table1
(ColA int NOT NULL, ColB decimal(10,3) NOT NULL);
GO
CREATE TABLE dbo.Table2
(ColA int PRIMARY KEY NOT NULL, ColB decimal(10,3) NOT NULL);
GO
INSERT INTO dbo.Table1 VALUES(1, 10.0);
INSERT INTO dbo.Table1 VALUES(1, 20.0);
INSERT INTO dbo.Table2 VALUES(1, 0.0);
GO
UPDATE dbo.Table2
SET dbo.Table2.ColB = dbo.Table2.ColB + dbo.Table1.ColB
FROM dbo.Table2
INNER JOIN dbo.Table1 ON (dbo.Table2.ColA = dbo.Table1.ColA);
GO
SELECT ColA, ColB
FROM dbo.Table2;
What difference am I missing between the two?
Your help is appreciated; thank you

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

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

Jack Corbett SSC Guru Points: 184394 More actions · Answer 1

You have a typo in your query. Check out the BOLD and CAPITALIZED stuff:

UPDATE dbo.tblClmsRx

SET dbo.tblClmsRx.RxPrescriberLName = LTRIM(RTRIM(dbo.tblPharmacyCLAIMSTemp.LastName))

FROM dbo.tblClmsRx

INNER JOIN dbo.tblPharmacyCLAIMTemp ON (dbo.tblClmsRx.RxClaimNum = dbo.tblPharmacyClaimTemp.RxClaimNum)

Claim is singular in the FROM and plural in the SET.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

rgtft Hall of Fame Points: 3518 More actions · Answer 2

Thank you! Thank you!! I looked at that a hundred times. I guess I just needed another pair of eyes.

Jack Corbett SSC Guru Points: 184394 More actions · Answer 3

Been there many times.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

Tomm Carr SSCertifiable Points: 5426 More actions · Answer 4

Simple errors like this can be eliminated by the widespread use of aliases.

UPDATE c

SET c.RxPrescriberLName = p.LastName

FROM dbo.tblClmsRx c

INNER JOIN dbo.tblPharmacyClaimTemp p

ON (c.RxClaimNum = p.RxClaimNum)

Thus each table name appears only once so there is less chance of a typo. To eliminate even that possibility, I also drag the table and the columns from the Object Explorer window and drop them on the editor window instead of typing the names myself. This is not because I'm lazy ... ok, not just because I'm lazy ... but because problems like this can be a bear to catch and this makes it so easy to avoid.

Tomm Carr
--
Version Normal Form -- http://groups.google.com/group/vrdbms