mysql case in update statement with REPLACE

I currently have something like this:

UPDATE table1  SET column1 = REPLACE(column1, 'abc', 'abc1') WHERE column1 LIKE '%abc%';
UPDATE table1  SET column1 = REPLACE(column1, 'def', 'def1') WHERE column1 LIKE '%def%';

I am trying to consolidate these into a single update statement and am trying the following:

UPDATE table1
SET column1 = 
CASE
WHEN column1 LIKE '%abc%' THEN REPLACE(column1, 'abc', 'abc1')
WHEN column1 LIKE '%def%' THEN REPLACE(column1, 'def', 'def1')
ELSE column1
END;

Is this the correct way of doing this? I am new to case/when. Thanks!

1 Answer

  1. Cosmo- Reply

    2020-01-24

    Since you are using LIKE '%abc%', the update statement will require a full table scan. In that case, combining the two statements will improve overall performance. However, in your suggestion, every single row is updated and most of them are updated without being changed (column1 value is replaced with column1 value).

    You want to make sure that you keep the WHERE clause so that only rows that really need change are changed. This unnecessary write to disk is slower than checking whether the row matches the criteria.

    Do this:

    UPDATE table1
    SET column1 = 
    CASE
    WHEN column1 LIKE '%abc%' THEN REPLACE(column1, 'abc', 'abc1')
    WHEN column1 LIKE '%def%' THEN REPLACE(column1, 'def', 'def1')
    END
    WHERE column1 LIKE '%abc%' OR column1 LIKE '%def%';
    

Leave a Reply

Your email address will not be published. Required fields are marked *

You can use these HTML tags and attributes <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>