Updating the same column in the same row multiple

问题:

I want to change all the words in the job_title.

In my example below the job_title is:

art department work experience/greenswoman in row 86619.

Here is the code:

Set autocommit = 1;

UPDATE tvt_job_title SET job_title = REPLACE(LOWER(job_title),'art','Art') WHERE job_title_id = 86619; UPDATE tvt_job_title SET job_title = REPLACE(LOWER(job_title),'work','Work') WHERE job_title_id = 86619; UPDATE tvt_job_title SET job_title = REPLACE(LOWER(job_title),'department','Department') WHERE job_title_id = 86619; UPDATE tvt_job_title SET job_title = REPLACE(LOWER(job_title),'experience','Experience') WHERE job_title_id = 86619; UPDATE tvt_job_title SET job_title = REPLACE(LOWER(job_title),'greenswoman','Greenswoman') WHERE job_title_id = 86619;

select job_title from tvt_job_title where job_title_id = 86619;

The result:

art department work experience/Greenswoman only Greenswoman gets corrected! Why don't all the words get corrected?

The output:

UPDATE tvt_job_title SET job_title = REPLACE(LOWER(job_title),'art','Art') WHERE job_title_id = 86619 1 row(s) affected Rows matched: 1 Changed: 1 Warnings: 0

UPDATE tvt_job_title SET job_title = REPLACE(LOWER(job_title),'work','Work') WHERE job_title_id = 86619 1 row(s) affected Rows matched: 1 Changed: 1 Warnings: 0

UPDATE tvt_job_title SET job_title = REPLACE(LOWER(job_title),'department','Department') WHERE job_title_id = 86619 1 row(s) affected Rows matched: 1 Changed: 1 Warnings: 0

UPDATE tvt_job_title SET job_title = REPLACE(LOWER(job_title),'experience','Experience'), abbreviation = REPLACE(LOWER(abbreviation),'experience','Experience') WHERE job_title_id = 86619 1 row(s) affected Rows matched: 1 Changed: 1 Warnings: 0

UPDATE tvt_job_title SET job_title = REPLACE(LOWER(job_title),'greenswoman','Greenswoman'), abbreviation = REPLACE(LOWER(abbreviation),'greenswoman','Greenswoman') WHERE job_title_id = 86619 1 row(s) affected Rows matched: 1 Changed: 1 Warnings: 0

select job_title from tvt_job_title where job_title_id = 86619 1 row(s) returned

回答1:

The behaviour is caused by the LOWER(job_title) function call in the update statements because it converts the entire job title lowercase, therefore eradicates the changes done by any previous updates in your code. You have to remove the lower() function from the code.

UPDATE tvt_job_title SET job_title = 
REPLACE(job_title,'work','Work') WHERE job_title_id = 86619

回答2:

MySQL's REPLACE() function is not case-insensitive.

I found this blog about a user who created a stored function to do case-insensitive string replace. It's awkward. https://pento.net/2009/02/15/case-insensitive-replace-for-mysql/

For this task, if you have special string comparison requirements, you might find it easier to just write a script to do it one row at a time.