Using SET UPDATE SET with WHERE Breaks

Question

IF OBJECT_ID('tempdb..#TempList') IS NOT NULL
    DROP TABLE #TempList

CREATE TABLE #TempList (
    VarName NVARCHAR(10) ,
    VarValue NVARCHAR(10),
    VarValueNext NVARCHAR(10)
)
INSERT INTO #TempList 
    VALUES ('Fred',NULL, NULL), 
           ('Wilma',NULL, NULL), 
           ('Barney',NULL, NULL), 
           ('Betty',NULL, NULL)

;WITH cte_name AS (
SELECT * 
    FROM #TempList
)

UPDATE cte_name
    SET cte_name.VarValue = 'Fred' WHERE cte_name.VarName = 'Wilma' OR cte_name.VarName = 'Barney'
--      , cte_name.VarValueNext = 'Pebbles' WHERE cte_name.VarValue = 'Fred' and cte_name.VarName = 'Wilma'

SELECT * 
    FROM #TempList

I understand that this is a simple example that does not require the use of a CTE construct. I am trying to understand how the UPDATE block affects.

I would like to set the value of the number of fields. As soon as I submit the WHERE clause, it seems that I can only set one value.

What am I doing wrong?

Thanks Thys

Answer 1

You need to use the case expression here not the where clause. Remember that the where clause filters the strings in your query. I think you are a little confused by the syntax of things. Here's what this type of update looks like.

UPDATE cte_name
    SET VarValue = case when VarName IN('Wilma', 'Barney') then 'Fred' else VarName end
      , VarValueNext = case when VarValue IN('Wilma', 'Barney') then 'Pebbles' else VarValueNext end

Answer 2

, -

UPDATE #TempList
SET    VarValue = CASE
                    WHEN VarName IN ( 'Wilma', 'Barney' ) THEN 'Fred'
                    ELSE VarValue
                  END,
       VarValueNext = CASE
                        WHEN VarValue IN ( 'Fred', 'Wilma' ) THEN 'Pebbles'
                        ELSE VarValueNext
                      END
WHERE  VarName IN ( 'Wilma', 'Barney' )
        OR VarValue IN ( 'Fred', 'Wilma' ) 

Answer 3

- WHERE UPDATE, SET.

, .

Thanks for helping everyone - I really appreciate it. A few days...; -)

IF OBJECT_ID('tempdb..#TempList') IS NOT NULL
    DROP TABLE #TempList

CREATE TABLE #TempList (
    VarName NVARCHAR(10) ,
    VarValue NVARCHAR(10),
    VarValueNext NVARCHAR(10)
)
INSERT INTO #TempList 
    VALUES ('Fred',NULL, NULL), 
           ('Wilma',NULL, NULL), 
           ('Barney',NULL, NULL), 
           ('Betty',NULL, NULL)

;WITH cte_name AS (
SELECT * 
    FROM #TempList
)

UPDATE cte_name
    SET cte_name.VarValue = 
                CASE
                    WHEN cte_name.VarName = 'Wilma' OR cte_name.VarName = 'Barney' 
                    THEN 'Fred'
                END 
        , cte_name.VarValueNext = 
                CASE
                    WHEN cte_name.VarName = 'Betty' OR cte_name.VarName = 'Wilma'
                    THEN 'Bosses'
                END 
SELECT * 
    FROM #TempList