Post reply

UPDATE Query

  • June 24, 2009 at 4:05 am

    #133436

    I need to input data into a column in a table using a reference table. Below is code displaying the scenario.

    -- REFERENCE TABLE

    CREATE TABLE #REF

    (

    Specialty varchar(20)

    ,Team varchar(20)

    ,Code varchar(20)

    )

    INSERT INTO #REF

    SELECT 'POD', 'HEALTH', 'SS18' UNION ALL

    SELECT 'POD', 'HEART', 'SS18' UNION ALL

    SELECT 'POD', 'DEFAULT', 'SS19'

    -- MAIN TABLE

    CREATE TABLE #SpecailtyTeam

    (

    Specialty varchar(20)

    ,SpecailtyDescription varchar(20)

    ,Team varchar(20)

    ,Code varchar(20)

    )

    INSERT INTO #SpecailtyTeam

    SELECT 'POD', 'PODIATRY', 'HEALTH', NULL UNION ALL

    SELECT 'POD', 'PODIATRY', 'HEART', NULL UNION ALL

    SELECT 'POD', 'PODIATRY', 'LEG', NULL UNION ALL

    SELECT 'POD', 'PODIATRY', 'NULL', NULL

    I want to update the Code column in the #SpecailtyTeam table with the code from the #REF table where Specialty and Team = the Specialty and Team columns in the #Ref table. This is straight forward enough.

    However the problem I have is how to update the code column if the Team value from #SpecailtyTeam is null or not found in the #REF table. In this case it should take the code where the Specialty value is the same but the team is called DEFAULT.

    For example. in the situation above in #SpecailtyTeam table the rows where the team = LEG and is null should be updated with the code SS19 as these teams are not found in the #REF table.

    How can I do this?

  • June 24, 2009 at 4:23 am

    #1014515

    Hi

    Thanks for the sample data. 🙂

    Try this:

    UPDATE st SET

    st.Code = ISNULL(r1.Code, r2.Code)

    FROM #SpecailtyTeam st

    JOIN #REF r2 ON r2.Team = 'DEFAULT'

    LEFT JOIN #REF r1 ON st.Team = r1.Team

    Flo

    The more I learn, the more I know what I do not know
    Blog: Things about Software Architecture, .NET development and T-SQL

    How to Post Data/Code to get the best Help How to Post Performance Problems

  • June 24, 2009 at 5:52 am

    #1014558

    Cheers!! Much appreciated

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

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