locked
Update Script RRS feed

  • Question

  • the below query returns 53 records

    select

    sheet001.FltDesg,[FlightMaster-12-12-13].FlightName,right(sheet001.NonOpFltDesg,len(sheet001.NonOpFltDesg)-3) from [FlightMaster-12-12-13]

    inner

    join sheet001

    on

    [FlightMaster-12-12-13].FlightName=right(sheet001.FltDesg,len(sheet001.FltDesg)-4)

    but when I give the same join for an update statement it updates only 14 records... I don't get whats wrong with the query... please help

    this is the update script

    UPDATE FM

    SET

    FM.ActualFlightName = right(s1.NonOpFltDesg,len(s1.NonOpFltDesg)-3)

    FROM

    [FlightMaster-12-12-13] FM

    INNER

    JOIN  sheet001 s1

    ON

      FM.FlightName=right(s1.FltDesg,len(s1.FltDesg)-4)

    Thursday, December 12, 2013 9:01 AM

Answers

  • Try Merge to see your issue:

     
    Merge [FlightMaster-12-12-13] FM
    Using sheet001 s1 On   FM.FlightName=right(s1.FltDesg,len(s1.FltDesg)-4)
    
    When Matched Then 
    Update Set ActualFlightName = right(s1.NonOpFltDesg,len(s1.NonOpFltDesg)-3)

    • Proposed as answer by Elvis Long Monday, December 23, 2013 11:21 AM
    • Marked as answer by Kalman Toth Friday, December 27, 2013 8:53 AM
    Thursday, December 12, 2013 3:48 PM

All replies

  • Can you post the results?

    SELECT
    
    FM.FlightName, right(s1.FltDesg,len(s1.FltDesg)-4)
    
    FROM
     
    
    [FlightMaster-12-12-13] FM
     
    INNER
     
    
    JOIN  sheet001 s1
     
    ON
     
    
      FM.FlightName=right(s1.FltDesg,len(s1.FltDesg)-4)
    


    Kalman Toth Database & OLAP Architect IPAD SELECT Query Video Tutorial 3.5 Hours
    New Book / Kindle: Exam 70-461 Bootcamp: Querying Microsoft SQL Server 2012


    Thursday, December 12, 2013 9:10 AM
  • When you do the select, if a row in [FlightMaster-12-12-13] matches multiple rows in sheet001, you will get multiple rows for that [FlightMaster-12-12-13] row in the result.

    But when you do an UPDATE, if there are multiple matches for your inner join, that [FlightMaster-12-12-13] is only updated once (SQL will pick one of the matching rows in sheet001 and use it for the update.  Note that in this case you don't kno which of the matching rows was used to do the update, and if you run the update twice, SQL might (or might not) pick a different matching row in sheet001). 

    So if there are multiple matches, the update will show fewer rows updated that the select will return.

    Tom

    Thursday, December 12, 2013 2:54 PM
  • Try Merge to see your issue:

     
    Merge [FlightMaster-12-12-13] FM
    Using sheet001 s1 On   FM.FlightName=right(s1.FltDesg,len(s1.FltDesg)-4)
    
    When Matched Then 
    Update Set ActualFlightName = right(s1.NonOpFltDesg,len(s1.NonOpFltDesg)-3)

    • Proposed as answer by Elvis Long Monday, December 23, 2013 11:21 AM
    • Marked as answer by Kalman Toth Friday, December 27, 2013 8:53 AM
    Thursday, December 12, 2013 3:48 PM