| Author |
 Topic  |
|
|
hbadministrator
Posting Yak Master
120 Posts |
 Posted - 04/02/2013 : 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]
|
Edited by - hbadministrator on 04/02/2013 09:03:08
|
|
|
hbadministrator
Posting Yak Master
120 Posts |
Posted - 04/02/2013 : 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 - 04/02/2013 : 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
Flowing Fount of Yak Knowledge
India
2242 Posts |
Posted - 04/02/2013 : 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 - 04/02/2013 : 09:30:34
|
| Perfect thank you! So really the only thing I had to do was make an Alias? |
|
|
| |
Topic |
|
|
|
The Multi-Part Identifier Error Please help