MySQL update part of 1 column in multiple rows

Question

I have table with a column "code" that has a value that looks like this for example

3_22_00418

I need to update this value to

3_01_00418

And I need to do this for all rows in my table

what I tried is the following:

UPDATE table SET
code = CASE
WHEN id='1' THEN '3_01_00418'
WHEN id='2' THEN '3_01_00519'
WHEN id='3' THEN '3_01_00647'
...

But this requires me to basically right all rows and I have hundreds of rows this will take a while.

How can I do it smarter?

How to get value of x? it's explain below: ... WHEN id='1' THEN '3_01_x' WHEN id='2' THEN '3_01_x' WHEN id='3' THEN '3_01_x' — Vikram Jain
– Vikram Jain, Commented Mar 22, 2019 at 8:39
I don't understand from where those replacement values are coming. Can you explain this? — Tim Biegeleisen
– Tim Biegeleisen, Commented Mar 22, 2019 at 8:41
@TimBiegeleisen the values are all set in my table the column "code" has a value for every row that looks something like this 3_22_00418 and the 22 is always the same and need to be replaced with 01 in all rows — Michael
– Michael, Commented Mar 22, 2019 at 8:43
@TimBiegeleisen basically I need a find an replace for _22_ to _01_ — Michael
– Michael, Commented Mar 22, 2019 at 8:45

Tim Biegeleisen · Accepted Answer · 2019-03-22 08:50:10Z

2

If you are using MySQL 8+, then the REGEXP_REPLACE function comes in handy here:

UPDATE yourTable
SET code = REGEXP_REPLACE(code, '(\\d+)_\d+_(\\d+)', '$1_01_$2');

Demo

If you are using an earlier version of MySQL, we can try using SUBSTRING_INDEX for a slightly bulkier looking update query:

UPDATE yourTable
SET code = CONCAT(SUBSTRING_INDEX(code, '_', 1), '_01_',
    SUBSTRING_INDEX(code, '_', -1));

Demo

edited Mar 22, 2019 at 8:50

answered Mar 22, 2019 at 8:46

Tim Biegeleisen

526k32 gold badges323 silver badges399 bronze badges

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

5 Comments

Onkar Musale Over a year ago

really awesome brother

Michael Over a year ago

the first solution says 0 rows affected, shouldnt I add a where clausule or something?

Tim Biegeleisen Over a year ago

@Michael You would only need a WHERE clause if you want to update certain rows. By the way, my answers are working with the sample data you have provided. As for other data, I can't verify that.

Michael Over a year ago

Yes I was using wrong version of MariaDB where your first solution doesn't work but the second one works fine. 1 little question how can I exclude rows where the "code" is empty?

Tim Biegeleisen Over a year ago

Use WHERE code IS NOT NULL, or, if you also want to exclude empty string, use WHERE code > ''

Vikram Jain · Accepted Answer · 2019-03-22 08:56:45Z

0

Using REPLACE function as per below:

SELECT REPLACE("3_01_00418", "_22_", "_01_");

EX: 
1) First way:

UPDATE table SET
code = REPLACE(code , "_22_", "_01_")
...

2) Second way:

  UPDATE table SET
     code = (case when id>=1 and id<= 100 then REPLACE(code , "_22_", "_01_")
                  when id>100 and id<= 200 then REPLACE(code , "_22_", "_02_")
                  else  REPLACE(code , "_22_", "_01_")
                  end
             )
    ...

answered Mar 22, 2019 at 8:56

Vikram Jain

5,6081 gold badge22 silver badges31 bronze badges

Collectives™ on Stack Overflow

MySQL update part of 1 column in multiple rows

2 Answers 2

Demo

Demo

5 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related