CODEWITHSUNDEEP
sqlsql-servert-sql
Richard77
Richard77

Reputation: 21651

Do I need a where clause in a conditional UPDATE?

We imported a lot of data from another table. Now I'm trying to correct some of them.

UPDATE [x10ddata].[dbo].[ResourceTest]
  SET [Country] = (CASE
      WHEN [Country] IN ('Aezerbaijan', 'AZERBIJAN') THEN 'Azerbaijan'
      WHEN [Country] = 'Belgique'    THEN 'Belgium'
      WHEN [Country] = 'China (RPC)' THEN 'China'
      WHEN [Country] = 'Columbia'    THEN 'Colombia'
     WHEN [Country] = 'Croatia (Local Name: Hrvatska)' THEN 'Croatia'
     .....//...
     WHEN [Country] IN ('U.S.', 'U.S.A', 'U.S.A.', 'US', 'USA', 
                         'USA - Maryland', 'USAQ') THEN 'United States'
    END)   
  GO

I didn't use ELSE because many rows have valid country. My question is to know whether I need to the WHERE clause to filter the rows that will be affected?

The reason I'm asking this question is that, I've selected into a test table and tried the the script. According to the output, all the rows affected, but when I check closely, not all the rows were affected. It's confusing.

Thanks for helping

Upvotes: 0

Views: 102

Answers (5)

Bob Jarvis - Слава Україні
Bob Jarvis - Слава Україні

Reputation: 50047

You won't need a WHERE clause, but the ELSE clause is needed. Change your statement to:

UPDATE [x10ddata].[dbo].[ResourceTest]
  SET [Country] = (CASE
      WHEN [Country] IN ('Aezerbaijan', 'AZERBIJAN') THEN 'Azerbaijan'
      WHEN [Country] = 'Belgique'    THEN 'Belgium'
      WHEN [Country] = 'China (RPC)' THEN 'China'
      WHEN [Country] = 'Columbia'    THEN 'Colombia'
     WHEN [Country] = 'Croatia (Local Name: Hrvatska)' THEN 'Croatia'
     .....//...
     WHEN [Country] IN ('U.S.', 'U.S.A', 'U.S.A.', 'US', 'USA', 
                         'USA - Maryland', 'USAQ') THEN 'United States'
     ELSE [Country]
    END)

Upvotes: 2

Jodrell
Jodrell

Reputation: 35726

alternatively,

Make a conversion table,

DECLARE @conversion TABLE
(
    [Before] NVARCHAR(250) NOT NULL,
    [After] NVARCHAR(250) NOT NULL
);

INSERT @conversion
VALUES
('Aezerbaijan', 'Azerbaijan'),
...
('USAQ', 'United States');

Then do,

UPDATE [x10ddata].[dbo].[ResourceTest]
            SET [Country] = [C].[After]
    FROM
            [x10ddata].[dbo].[ResourceTest]
        JOIN
            @conversion [C]
                ON [C].[Before] = [C].[Country];

This has a number of potential performance benefits over the extend CASE approach, among which is only effecting rows that need to change.

Its probably worth using a temporary table instead of a table variable and creating an index on [Before] to optimize the join.

Upvotes: 1

Biscuits
Biscuits

Reputation: 1807

No, you don't NEED it. Aside from the performance cost that may be incurred through additional (unnecessary) writes to disk and locking (blocking other sessions), the physical outcome would be the same.

One could argue that you SHOULD use a WHERE clause, not only for performance reasons, but to better capture and convey intentions.

Upvotes: 0

Stephan
Stephan

Reputation: 6018

No you don't need a where clause because your CASE statements contain your logic.

Note: If a value doesn't match any of your CASE statements then it will return null. So I recommend adding ELSE [Country] at the end. Here's an example that demonstrates what I'm saying

SELECT * INTO #yourTable
FROM
(
    SELECT 1 ID, CAST('OldValue' AS VARCHAR(25)) val
    UNION ALL
    SELECT 2 , 'OldValue'
    UNION ALL
    SELECT 3,'Doesnt need to be updated'
) A

SELECT *
FROM #yourTable;

Results:

ID          val
----------- -------------------------
1           OldValue
2           OldValue
3           Doesnt need to be updated

Now update:

UPDATE #yourTable
SET val = 
    CASE 
        WHEN ID = 1 THEN 'NewValue1'
        WHEN ID = 2 THEN 'NewValue2'
        --Add this so you leave values alone if they don't match your case statements
        ELSE val
    END
FROM #yourTable


SELECT *
FROM #yourTable

Results:

ID          val
----------- -------------------------
1           NewValue1
2           NewValue2
3           Doesnt need to be updated

Upvotes: 0

David
David

Reputation: 34563

The case statement will return null if none of the when clauses are met. You can verify this with this simple sql:

declare @i int
set @i = 2
select case when @i = 1 then 'A' end AS Column1

This will return null since @i is not 1.

To fix this in your case, you can either add the where clause like you said, or the simpler option might be to add ELSE [Country] after all of your WHEN clauses. This would mean "If I don't need to change the country field, then just use the same value that was there before."

Upvotes: 3

Related Questions