The Multi-Part Identifier Error Please help

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

The Multi-Part Identifier Error Please help

Author

Topic

hbadministrator
Posting Yak Master

120 Posts

Posted - 2013-04-02 : 08:30:39

I have 2 tables first table I have a set where statement that filters out all the fields that have blank info and it filled from another table. Here is the sql code for the select part I just don't know how to write the update part.

SELECT [Cust-no], PreBalance, [From-cust], Reference, budgetpayment, [Original-amt], Baldue, [Balance-amt], [Seq-no], Memo, [Cust-po], date, [Trans-code], Credit, Debit,
[Misc-Code], ChargeName, ChargeAdd, ChargeCity, ChargeState, ChargeZip, SiteName, SiteAdd, SiteCity, SiteState, SiteZip
FROM MarchStatementData
WHERE ([From-cust] = [Cust-no]) AND (ChargeName = '')

This returns all Cust-no that have all ChargeName that are blank.
I need to fill in the blank Fields: ChargeName, ChargeAdd, ChargeCity, ChargeState, ChargeZip, SiteName, SiteAdd, SiteCity, SiteState, SiteZip from the Customer table. the common Field is [Cust-no]. Then the customer table has fields called Name, Address, City, State, Zip. I need those to update all the Charge's and Site fields. Example see below

SELECT
[Cust-no],
PreBalance,
[From-cust],
Reference,
budgetpayment,
[Original-amt],
Baldue,
[Balance-amt],
[Seq-no],
Memo,
[Cust-po],
date,
[Trans-code],
Credit,
Debit,
[Misc-Code],
ChargeName,
ChargeAdd,
ChargeCity,
ChargeState,
ChargeZip,
SiteName,
SiteAdd,
SiteCity,
SiteState,
SiteZip

FROM MarchStatementData

WHERE ([From-cust] = [Cust-no]) AND (ChargeName = '')

Update MarchStatementData

Set
ChargeName = Customer.name,
ChargeAdd = Customer.address,
ChargeCity = Customer.City,
ChargeState = Customer.State,
ChargeZip = Customer.Zip,
SiteName = Customer.name,
SiteAdd = Customer.address,
SiteCity = Customer.City,
SiteState = Customer.State,
SiteZip = Customer.zip

From MarchStatementData inner join customer on customer.[cust-no] = MarchstatementData.[cust-no]

hbadministrator
Posting Yak Master

120 Posts

Posted - 2013-04-02 : 08:43:57

Here is how I think it should read.

SELECT [Cust-no], PreBalance, [From-cust], Reference, budgetpayment, [Original-amt], Baldue, [Balance-amt], [Seq-no], Memo, [Cust-po], date, [Trans-code], Credit, Debit,
[Misc-Code], ChargeName, ChargeAdd, ChargeCity, ChargeState, ChargeZip, SiteName, SiteAdd, SiteCity, SiteState, SiteZip
FROM Service.dbo.MarchStatementData
WHERE ([From-cust] = [Cust-no]) AND (ChargeName = '')

update Service.dbo.MarchStatementData
set MarchStatmentData.ChargeName = Customer.name,
MarchStatmentData.ChargeAdd = Customer.address,
MarchStatmentData.ChargeCity = Customer.City,
MarchStatmentData.ChargeState = Customer.St,
MarchStatmentData.ChargeZip = Customer.[Zip-code],
MarchStatmentData.SiteName = Customer.name,
MarchStatmentData.SiteAdd = Customer.address,
MarchStatmentData.SiteCity = Customer.City,
MarchStatmentData.SiteState = Customer.St,
MarchStatmentData.SiteZip = Customer.[Zip-Code]
From
Service.dbo.MarchStatementData
inner join Service.dbo.customer on Service.dbo.customer.[Cust-no] = Service.dbo.MarchStatementData.[Cust-no]

hbadministrator
Posting Yak Master

120 Posts

Posted - 2013-04-02 : 08:44:32

but I get this error.

Msg 4104, Level 16, State 1, Line 6
The multi-part identifier "MarchStatmentData.ChargeName" could not be bound.

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-04-02 : 09:15:05

-- Try this
update msd
set msd.ChargeName = c.name,
msd.ChargeAdd = c.address,
msd.ChargeCity = c.City,
msd.ChargeState = c.St,
msd.ChargeZip = c.[Zip-code],
msd.SiteName = c.name,
msd.SiteAdd = c.address,
msd.SiteCity = c.City,
msd.SiteState = c.St,
msd.SiteZip = c.[Zip-Code]
From
Service.dbo.MarchStatementData msd
inner join Service.dbo.customer c on c.[Cust-no] = msd.[Cust-no]

hbadministrator
Posting Yak Master

120 Posts

Posted - 2013-04-02 : 09:30:34

Perfect thank you! So really the only thing I had to do was make an Alias?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources