Update using CASE: want to learn what CASE did w/out Select stmt

  • Just wondering if there is any way to find out what the CASE produces without doing a select after the UPDATE. Is this possible?

    UPDATE myTable

    set myField=

    CASE

    WHEN myField='A'

    THEN 'X'

    ELSE 'D'

    END

    WHERE myID= 100

    In other words, is there anyway to capture the new value of a field is by doing something to the UPDATE stmt?

    Thanks.

  • If you just want to capture new value in a variable:

    DECLARE @newValue VARCHAR(10)

    UPDATE myTable

    set @newValue=myField=

    CASE

    WHEN myField='A'

    THEN 'X'

    ELSE 'D'

    END

    WHERE myID= 100

  • vfazal (2/8/2010)


    If you just want to capture new value in a variable:

    DECLARE @newValue VARCHAR(10)

    UPDATE myTable

    set @newValue=myField=

    CASE

    WHEN myField='A'

    THEN 'X'

    ELSE 'D'

    END

    WHERE myID= 100

    Most of the time I just do SELECT directly

    but temp variable is a good alternative

    SELECT myField=

    CASE

    WHEN myField='A'

    THEN 'X'

    ELSE 'D'

    END

    FROM table

    WHERE myID= 100

    SQLServerNewbieMCITP: Database Administrator SQL Server 2005
  • vfazal,

    Thank you! That is exactly what I wanted. Could you tell me how you learned of this method?

    Jerry,

    Thank you, but I was looking for a method to get the info without using a SELECT stmt against the DB and only wanted to capture the info in the UPDATE stmt somehow.

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

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