SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 The Multi-Part Identifier Error Please help
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

hbadministrator
Posting Yak Master

120 Posts

Posted - 04/02/2013 :  08:30:39  Show Profile  Reply with Quote
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]




Edited by - hbadministrator on 04/02/2013 09:03:08

hbadministrator
Posting Yak Master

120 Posts

Posted - 04/02/2013 :  08:43:57  Show Profile  Reply with Quote
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]
Go to Top of Page

hbadministrator
Posting Yak Master

120 Posts

Posted - 04/02/2013 :  08:44:32  Show Profile  Reply with Quote
but I get this error.

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

bandi
Flowing Fount of Yak Knowledge

India
2223 Posts

Posted - 04/02/2013 :  09:15:05  Show Profile  Reply with Quote
-- 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]
Go to Top of Page

hbadministrator
Posting Yak Master

120 Posts

Posted - 04/02/2013 :  09:30:34  Show Profile  Reply with Quote
Perfect thank you! So really the only thing I had to do was make an Alias?
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000