Post reply

Updating multiple records in a single column

  • June 29, 2011 at 2:43 am

    #238657

    Hi All,

    I am trying to update one of my column using update statement but when i executed my query i got following error:

    "Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression."

    My query is :

    update target_segment

    set geo_id = (select gd.GEO_ID from geo_dim gd

    left outer join Target_Segment T ON T.[Geography_Name]= GD.SubsidiaryName

    WHERE T.GEOGRAPHY_LEVEL_NAME ='Subsidiary')

    Initially geo_id is set to null during ETL , but later it needs to be updated using update statement. Please let me know an appropriate solution for the same. Thanks in Advance

    Cheers!

    Abhishek

  • June 29, 2011 at 2:54 am

    #1345727

    The cause of the error is pretty self-evident, so I won't labour that point. Using the MERGE syntax may help you write the query more naturally, and make it easier for you to see where you are going wrong.

    MERGE Target_Segment AS ts

    USING geo_dim AS gd

    ON gd.SubsidiaryName = ts.Geography_Name

    WHEN MATCHED

    AND ts.GEOGRAPHY_LEVEL_NAME ='Subsidiary'

    THEN UPDATE SET geo_id = gd.GEO_ID;

    Paul White
    SQLPerformance.com
    SQLkiwi blog
    @SQL_Kiwi

  • June 29, 2011 at 3:38 am

    #1345747

    Awesome man....Thanks a lot:-)

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply