144

I need a SQL update statement for updating a particular field of all the rows with a string "test" to be added in the front of the existing value.

For example, if the existing value is "try" it should become "testtry".

Spontifixus
  • 6,570
  • 9
  • 45
  • 63
santanu
  • 5,217
  • 7
  • 26
  • 17

5 Answers5

294

You can use the CONCAT function to do that:

UPDATE tbl SET col=CONCAT('test',col);

If you want to get cleverer and only update columns which don't already have test prepended, try

UPDATE tbl SET col=CONCAT('test',col)
WHERE col NOT LIKE 'test%';
Paul Dixon
  • 295,876
  • 54
  • 310
  • 348
18
UPDATE tablename SET fieldname = CONCAT("test", fieldname) [WHERE ...]
Ferdinand Beyer
  • 64,979
  • 15
  • 154
  • 145
  • update TICKET set status_details = CONCAT(status _details,'abc') where ticket_id=75108; ERROR 1583 (42000): Incorrect parameters in the call to native function 'CONCAT' – nirmesh khandelwal Jul 01 '13 at 13:45
13

Many string update functions in MySQL seems to be working like this: If one argument is null, then concatenation or other functions return null too. So, to update a field with null value, first set it to a non-null value, such as ''

For example:

update table set field='' where field is null;
update table set field=concat(field,' append');
Janci
  • 3,198
  • 1
  • 21
  • 22
bvidinli
  • 131
  • 1
  • 2
7

That's a simple one

UPDATE YourTable SET YourColumn = CONCAT('prependedString', YourColumn);
soulmerge
  • 73,842
  • 19
  • 118
  • 155
  • Correct up to the WHERE clause, where you only concat test to columns already starting with test. So: foo -> foo footest -> footest testfoo -> testtestfoo – Jukka Dahlbom Mar 25 '09 at 09:22
1
  • UPDATE table_name SET Column1 = CONCAT('newtring', table_name.Column1) where 1
  • UPDATE table_name SET Column1 = CONCAT('newtring', table_name.Column2) where 1
  • UPDATE table_name SET Column1 = CONCAT('newtring', table_name.Column2, 'newtring2') where 1

We can concat same column or also other column of the table.

user3419778
  • 856
  • 3
  • 8
  • 11