SQL: Replace two values in multiple rows in a single column

Question

I have a column with values '< 500' and '> 500' in several hundred rows. Those are the only two figures. Now, I want to replace all '< 500' with 'xx' and all '> 500' with 'yy'.

Is there any way to replace both the values in a single SQL query rather than using two separate update queries as below:

UPDATE [table] SET [column] = 'xx' WHERE [column] = '< 500';
UPDATE [table] SET [column] = 'yy' WHERE [column] = '> 500';

I tried using Case for this, but couldn't get it working.

Your syntax suggests SQL Server so I removed the oracle tag. — Gordon Linoff
– Gordon Linoff, Commented Apr 22, 2016 at 2:49

Gordon Linoff · Accepted Answer · 2016-04-22 02:50:41Z

3

Yes, use a case statement:

UPDATE [table]
    SET [column] = (CASE WHEN [column] = '< 500' THEN 'xx' ELSE 'yy' END)
    WHERE [column] IN ('< 500', '> 500');

answered Apr 22, 2016 at 2:50

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Liesel Over a year ago

You type faster than me @Gordon :)

Collectives™ on Stack Overflow

SQL: Replace two values in multiple rows in a single column

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related