MySql / PHP - 更新多行

Question

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.

Answer 1

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.