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  |
|
|
|