Leo Leo - 2 years ago 75
SQL Question

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

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.

Answer Source

Yes, use a case statement:

UPDATE [table]
    SET [column] = (CASE WHEN [column] = '< 500' THEN 'xx' ELSE 'yy' END)
    WHERE [column] IN ('< 500', '> 500');
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download