2

I have a populated MySQL TABLE where I need to pre-append the text "NEW_" for the name field. (Long story, but this is what needs to be done for now.)

I imagine this can be done with an UPDATE but I'm not sure how to add "NEW_" to the start of the 'name' column. I also work in PHP, so I need to do this in PHP where I read each record, extract 'name' field, add this and then do an UPDATE for the record? Or can this all be done in MySQL?

afuzzyllama
  • 6,538
  • 5
  • 47
  • 64
Edward
  • 9,430
  • 19
  • 48
  • 71

3 Answers3

4

use this query

UPDATE table_name set `name` = CONCAT('NEW_',`name`)
Yogesh Suthar
  • 30,424
  • 18
  • 72
  • 100
  • Why does name appear as `name` in your example? What is the difference between not using ` surrounding the field name? Thanks! – Edward Apr 20 '13 at 07:50
  • @Edward using ` around column_name is important when you have used mysql reserved words as column_name. For example group is reserved word in mysql. If you use only `select group from tbl_tp` It will generate error. But if you use **select ` ``group` `` from tbl_tp**, It will not generate error and query will be executed. – Yogesh Suthar Apr 20 '13 at 07:56
  • 2
    I see, so it's a way to protect against reserved words without having to memorize what all the reserved words are by just putting it into practice to put ` around fields. Thanks for explaining that! – Edward Apr 20 '13 at 08:11
2

You can use CONCAT like this:

UPDATE tbl SET name=CONCAT('NEW_',name)
WHERE name NOT LIKE 'NEW\_%';

The second line will stop this operating on columns with NEW_ already at the start.

Glitch Desire
  • 14,632
  • 7
  • 43
  • 55
  • There is a problem with this. A 'name' field which contained data such as "New Time Bar", doesn't get changed to "NEW_New Time Bar". – Edward Apr 20 '13 at 07:42
  • Hmm, yes, forgot that _ is a wildcard character. You'd have to use `NEW\_` to see it as a literal. – Glitch Desire Apr 20 '13 at 07:44
0

You can just do it like this:

UPDATE myTable SET name = CONCAT('NEW_',name);

As posted before by myself this will not work:

UPDATE myTable SET name = 'NEW_' + name;

das Keks
  • 3,723
  • 5
  • 35
  • 57