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, SiteZipFROM MarchStatementDataWHERE ([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 belowSELECT [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, SiteZipFROM MarchStatementDataWHERE ([From-cust] = [Cust-no]) AND (ChargeName = '')Update MarchStatementDataSetChargeName = 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.zipFrom 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, SiteZipFROM Service.dbo.MarchStatementDataWHERE ([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]FromService.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 6The 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 thisupdate msdset 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]FromService.dbo.MarchStatementData msdinner 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? |
|
|
|
|
|