MySql / PHP - 更新多行

I have a column named 'unit' in my db. The value of all (several hundred) entries are entered as "12.Z" or "16.Z" etc.

I would like to update all values to "12 OZ" or "16 OZ".
I.e. if the value is 12.Z it should be changed to 12 OZ.

I have no problem updating multiple rows in one query, however I'm not sure how to begin (or end) this query.

Use this query:

UPDATE <TABLENAME> SET <COLUMNNAME>=REPLACE(<COLUMNNAME>, '.Z', 'OZ') WHERE <COLUMNNAME> LIKE '%.Z'

• is the name of the table you want to update (remove the <> chars)
• is the name of the column in the table you want to update (remove <>)

Replace is a MySQL function to replace characters in a string (https://www.w3resource.com/mysql/string-functions/mysql-replace-function.php)

LIKE is the search operator. You are only looking for values ending with .Z. The % is a wildcard to have anything at the beginning.

You can use an update as

You can use two separated query

update my_table 
set my_col = '12 OZ' 
where my_col = '12.Z'
;


update my_table 
set my_col = '16 OZ' 
where my_col = '16.Z'
; 

or use a single query with case when

update my_table 
set case when my_col = '12.Z' then  '12 OZ'
          when my_col = '16.Z' then  '16 OZ'
where my_col in ('12.Z', '16.Z')
;

Update multiple rows in mysql

Syntax

"UPDATE table_name SET column_name1=' value[$array]', column_name2=' value[$array]' WHERE column_name=' value[$array]' ";

Please Try this link

Thanks