CODEWITHSUNDEEP
mysqlsql
Michael
Michael

Reputation: 576

MySQL update part of 1 column in multiple rows

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?

Upvotes: 0

Views: 159

Answers (2)

Vikram Jain
Vikram Jain

Reputation: 5588

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
             )
    ...

Upvotes: 0

Tim Biegeleisen
Tim Biegeleisen

Reputation: 520948

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

Upvotes: 2

Related Questions