Add a prefix to all values of a column in a MYSQL table

Question

I have a mysql database table where I need to add the prefix "35-" to all values of a certain column/field (productcode) whose category number is higher tahn 500. So I would for example want productcode value 123A56B789 be changed to 35-123A56B789, and similar in all other rows of the table.

An additional problem is that this column is the primary key of that table, but I tried to work around that by adding an additional index column, defining that as the primary key, erasing the index on "productcode" and trying the following query (with the intention to later define it as a primary index again):

UPDATE my_table 
SET productcode = '35-' + productcode
WHERE category >= 500

I get no error or warning messages (even a message "xxxx rows affected"), but there is no visible effect whatsoever - the productcodes stay the same as they were before.

Is there a method to achieve this directly in MYSQL?

Its a long shot but try to add a where clause. `WHERE 1 = 1` or something like that. — clinomaniac, Mar 14 '18 at 23:37
Also, did you look at https://stackoverflow.com/a/33971431/3179169? — clinomaniac, Mar 14 '18 at 23:38
@clinomaniac Actually, I do have a WHERE clause in my query, I added it now to the question. But I don't really know what you are referring to in your comments... — Johannes, Mar 14 '18 at 23:42
I was just going after the `safe updates` check. But since you have the where clause, it doesn't apply. — clinomaniac, Mar 14 '18 at 23:44

score 5 · Accepted Answer · answered Mar 14 '18 at 23:42

5

You can use CONCAT to add 35- to the start of your productcode column. Check it.

UPDATE my_table SET productcode = CONCAT('35-', productcode) WHERE category >= 500;

answered Mar 14 '18 at 23:42

whatthefish

347
1
17

perfect, that's exactly what I was looking for - thank you very much! – Johannes Mar 14 '18 at 23:46
You're welcome. If it worked, please accept the answer. Thanks. – whatthefish Mar 14 '18 at 23:48

Add a prefix to all values of a column in a MYSQL table

1 Answers1