Updating fields in one table that are not empty

Question

I got this SQL:

UPDATE users2 
SET picture = 'sites/site2/files/pictures/' + picture;
WHERE picture NOT IS NULL

And the only thing I get are that all picture fields get the value '0'.

Answer 1

In MySQL + means numerical addition. Your strings are being cast to integers, added, and then the result converted back to a string.

Use CONCAT for string concatenation. Here's a fixed version of your query:

UPDATE users2 
SET picture = CONCAT('sites/site2/files/pictures/', picture)
WHERE picture IS NOT NULL

Answer 2

Because adding does not work for strings. Use CONCAT() instead:

UPDATE users2 
SET picture = CONCAT('sites/site2/files/pictures/', picture)
WHERE pictures NOT IS NULL

Also, notice you have a semi-colon in the middle of the query... remove it or you'll get all rows updated!