CODEWITHSUNDEEP
sql
Noobdude
Noobdude

Reputation: 11

How to update multiple rows in one column in one query SQL

I have a table with the following customer order data. I am trying to clean up one column that shows the state the customer is in. This column has a mix of full state names and their abbreviations and I want to just have the abbreviations. Below is the gist of what I came up with but I'm not sure what's wrong. I have to end up doing this for basically every state.

UPDATE customer_orders 
SET state = CASE 
   WHEN 'Colorado' THEN 'CO'
   WHEN 'Wisconsin' THEN 'WI'
END 
WHERE state IN ('Colorado', 'Wisconsin');

Upvotes: 1

Views: 353

Answers (3)

Ramil Aliyev 007
Ramil Aliyev 007

Reputation: 5452

CASE WHEN Statement required boolean condition after WHEN. If you don't it, you get this error:

An expression of non-boolean type specified in a context where a condition is expected, near 'THEN'.

Therefore you must compare state in when condition like as below:

UPDATE customer_orders 
SET state = CASE 
   WHEN state = 'Colorado' THEN 'CO'
   WHEN state = 'Wisconsin' THEN 'WI'
END 
WHERE state IN ('Colorado', 'Wisconsin');

For more information about usage CASE statement in UPDATE, you can visit this link: Usage CASE statement in UPDATE

Upvotes: 0

Derek Butler
Derek Butler

Reputation: 101

You would just build a CASE statement that covers all states.

UPDATE customer_orders SET state =
   CASE WHEN state = 'Colorado' THEN 'CO'
        WHEN state = 'Wisconsin' THEN 'WI'
        ....
        ELSE state
   END;

You technically don't have to filter by a where statement unless you have other junk data in the state column that you don't want to touch.

Upvotes: 1

Charlieface
Charlieface

Reputation: 71579

You could join onto a VALUES table constructor, with state names and codes.

Like this:

UPDATE c
SET state = s.code
FROM customer_orders AS c
JOIN (VALUES
    ('CO','Colorado),
    ('WI','Wisconsin'),
    -- .....etc
) AS s(code,name) ON s.name = c.state;

Upvotes: 2

Related Questions