#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2012
    Posts
    4
    Rep Power
    0

    SQL Update with an Inner Join


    Hey folks,

    I'm obviously an amateur when it comes to SQL. I wanted to do this in Access, but the process is too large and complex so that was not an option. I'm using SQL Server Management Studio. What I want to do is pretty simple:

    I have two tables (dbo.PolicyNum_Company_MZ and dbo.TEMP_AIR_HU_PolicyEventLoss). I want to join them based on alike columns.

    - The columns I want to join are: POLICYNUM in dbo.PolicyNum_Company_MZ and strID in dbo.TEMP_AIR_HU_PolicyEventLoss

    I have two additional columns in dbo.TEMP_AIR_HU_PolicyEventLoss that I want to update with data from dbo.PolicyNum_Company_MZ.

    - These columns are Microzone (which I want to update with MICROZONE from dbo.PolicyNum_Company_MZ) and Company (which I want to update with COMPANY from dbo.PolicyNum_Company_MZ).

    Thanks so much for your help in advance and let me know if there is any more information that could be helpful.
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,026
    Rep Power
    4210
    Code:
    UPDATE dbo.TEMP_AIR_HU_PolicyEventLoss
       SET Microzone = t2.MICROZONE
         , Company = t2.COMPANY
      FROM dbo.TEMP_AIR_HU_PolicyEventLoss t1
    INNER
      JOIN dbo.PolicyNum_Company_MZ t2
        ON t1.strID = t2.POLICYNUM

    Comments on this post

    • boulderrr agrees : You rock! Thanks!
    r937.com | rudy.ca
    please visit Simply SQL and buy my book
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2012
    Posts
    4
    Rep Power
    0
    Originally Posted by r937
    Code:
    UPDATE dbo.TEMP_AIR_HU_PolicyEventLoss
       SET Microzone = t2.MICROZONE
         , Company = t2.COMPANY
      FROM dbo.TEMP_AIR_HU_PolicyEventLoss t1
    INNER
      JOIN dbo.PolicyNum_Company_MZ t2
        ON t1.strID = t2.POLICYNUM
    Thanks so much! I was hoping I'd be able to figure the next step out by myself, but am having some trouble.

    Now that I have those columns populated, I want to create a new table grouped by several columns

    I'm now only using the table dbo.TEMP_AIR_HU_PolicyEventLoss.

    - I want to group by columns Company2, Microzone2, intModel, intYear, and intEvent
    - I want the column EventLossGR to be summed based on the grouping above
    - In a new table, I want to display Company2, Microzone2, intModel, intYear, intEvent, and the sum of EventLossGR

    I think I may be close, but am getting an error. Right now, I have:

    SELECT Microzone2, UniqueEvent, intModel, intYear, intEvent, Company2, SUM(EventLossGR)
    INTO dbo.EventLossbyMZ
    FROM dbo.TEMP_AIR_HU_PolicyEventLoss
    GROUP BY Microzone2, UniqueEvent, intModel, intYear, intEvent, Company2
    The error I'm getting says:
    Msg 1038, Level 15, State 5, Line 1 An object or column name is missing or empty. For SELECT INTO statements, verify each column has a name. For other statements, look for empty alias names. Aliases defined as "" or [] are not allowed. Change the alias to a valid name.
    Thanks again for all the assistance!
  6. #4
  7. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,026
    Rep Power
    4210
    Originally Posted by boulderrr
    For SELECT INTO statements, verify each column has a name.
    Code:
    SELECT Microzone2
         , UniqueEvent
         , intModel
         , intYear
         , intEvent
         , Company2
         , SUM(EventLossGR) AS sum_eventlossGR
      INTO dbo.EventLossbyMZ 
      FROM dbo.TEMP_AIR_HU_PolicyEventLoss 
    GROUP 
        BY Microzone2
         , UniqueEvent
         , intModel
         , intYear
         , intEvent
         , Company2

    Comments on this post

    • boulderrr agrees : I was so close! Thanks again! :)
    r937.com | rudy.ca
    please visit Simply SQL and buy my book

IMN logo majestic logo threadwatch logo seochat tools logo