Update records in the database table based on select/join query

Hello there,

I wrote a query and exported the result to excel, then in the excel sheet I correct some columns which had issues in them. Now my data is correct in the excel but now my problem is how do i again send these data to my mssql 2008 database table. I need to some how update those columns in my table with this excel sheet based on my original select query.Please help.

cheers
zolfAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Guy Hengel [angelIII / a3]Billing EngineerCommented:
you have several options...
depending on the data i would either load the excel file to a staging table, and update with join
or using formulas in excel build the update statments...
0
zolfAuthor Commented:
Thanks angel for your comments.
I managed to create a table in my database and loaded that table with my excel data. Now what do i do to update my table with this col. it is little complex as i mentioned.
0
zolfAuthor Commented:
this is my original sql query which i then exported to excel and did my correction in the excel. now i need to update these columns

ISNULL(Customer.registerCode, '') as registerCode,
ISNULL(Customer.economalCode, '') as economicCode,
ISNULL(CustomerOwnerInfo.melliCode, '') as melliCode,


SELECT DISTINCT
    RIGHT('000' + CAST(kol.code AS VARCHAR), 3) + RIGHT('000' + CAST(mohin.code AS VARCHAR), 3) +
    RIGHT('0000' + CAST(tafsil.code AS VARCHAR), 4) AS code,
    LTRIM(RTRIM(dbo.Customer.printName)) AS printName,
    LTRIM(RTRIM(dbo.Customer.printNameAlternate))  AS printNameFarsi,
    ISNULL(dbo.City.alternateName, '') + ', ' + ISNULL(dbo.Address.streetAlternate,'') AS addr,
    dbo.customertype.name as customertypename,
    dbo.customertype.alternateName as customertypepername,
    ISNULL(ContactInformation.phoneNumber, '') as phoneNo,
          ISNULL(ContactInformation.mobileNumber, '') as mobileNo,
          ISNULL(ContactInformation.faxNumber, '') as faxNo,
          ISNULL(ContactInformation.email, '') as email,
          ISNULL(Customer.registerCode, '') as registerCode,
    dbo.Customer.organizationNameAlternate as organizationName,
    ISNULL(Customer.economalCode, '') as economicCode,
          ISNULL(CustomerOwnerInfo.melliCode, '') as melliCode,
          ISNULL(Address.postCode, -1) as postCode,
          ISNULL(CustomerOwnerInfo.firstNameAlternate, '') as firstName,
          ISNULL(CustomerOwnerInfo.lastNameAlternate, '') as lastName,
    branch.name,
    branch.alternatename,
    Customer.organizationNameAlternate + ' ('+customertype.alternateName+')'+' '+'-'+' '+PersonTitle.alternateName+' '+CustomerOwnerInfo.lastNameAlternate as kaizencustomername,
    dbo.Visitor.firstName1 as visit_firstName1,
    dbo.Visitor.firstName2 as visit_firstName2,
    dbo.Visitor.lastName1 as visit_lastName1,
    dbo.Visitor.lastName2 as visit_lastName2
    
FROM
    dbo.Customer
LEFT JOIN
    dbo.CustomerOwnerInfo
ON
    (
        dbo.Customer.ownerInfoId = dbo.CustomerOwnerInfo.id)
        LEFT JOIN
    dbo.PersonTitle
ON
    (
        dbo.PersonTitle.id = dbo.CustomerOwnerInfo.persontitleid)
LEFT JOIN
    dbo.ContactInformation
ON
    (
        dbo.Customer.organizationContactInformationId = dbo.ContactInformation.id)
LEFT JOIN
    dbo.Tafsil
ON
    (
        dbo.Customer.tafsilId = dbo.Tafsil.id)
LEFT JOIN
    dbo.Mohin
ON
    (
        dbo.Tafsil.mohinId = dbo.Mohin.id)
LEFT JOIN
    dbo.Kol
ON
    (
        dbo.Mohin.kolId = dbo.Kol.id)
LEFT JOIN
    dbo.Address
ON
    (
        dbo.ContactInformation.addressId = dbo.Address.id)
LEFT JOIN
    dbo.RouteCustomerLink
ON
    (
        dbo.Customer.id = dbo.RouteCustomerLink.customerId)
LEFT JOIN
    dbo.Route
ON
    (
        dbo.RouteCustomerLink.routeId = dbo.Route.id)
LEFT JOIN
    dbo.Visitor
ON
    (
        dbo.Route.visitorId = dbo.Visitor.id)
LEFT JOIN
    dbo.City
ON
    (
        dbo.Address.cityId = dbo.City.id) 
        LEFT JOIN
    dbo.customertype
ON
    (
        dbo.customer.customertypeid = dbo.customertype.id)
        LEFT JOIN
        dbo.ContactInformation as ci
ON
    (
        dbo.Customerownerinfo.contactInformationId = dbo.ContactInformation.id)
        LEFT JOIN
    dbo.branch
ON
    (
        dbo.branch.Id = dbo.Tafsil.branchid)

Open in new window

0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
normally, a "update with join" syntax would be done by reading up this article:
http://www.experts-exchange.com/articles/1517/UPDATES-with-JOIN-for-everybody.html

however, I am not sure that applies here, as your data is in excel, updated manually from the export using that join select.
the "updates" you want to do are not in regards to that "output", but in regards in to individual table rows.

so I have to repeat that either you write (with excel formulas) the updates or using the update with join above from the data loaded into a staging table from the excel file.
the main "key" to success is that you need the "primary key" fields of the different tables in your excel file, otherwise you will not know which row(s) to update in any case
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
zolfAuthor Commented:
thanks
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.