CODEWITHSUNDEEP
mysql
Homer_J
Homer_J

Reputation: 3323

MySQL UPDATE not working when text similar

UPDATE: It looks like MySQL ignores the trailing space on the query 'Media Services '

I have the following code:

SELECT * from table WHERE company = 'Media Services '

This brings back 132 rows (note the space at the end of the company name). I need to remove this space, so I have tried using the following code:

UPDATE table SET company = 'Media Services' WHERE company = 'Media Services '

The output is 0 rows affected - trying to understand why that might be?

Any ideas and suggestions as I don't really want to manually change 132 records.

Upvotes: 0

Views: 34

Answers (3)

Rahul
Rahul

Reputation: 77876

Not sure but try using LIKE operator instead like

UPDATE table SET company = 'Media Services' 
WHERE company LIKE 'Media Services%'

Per your latest comment, it's my strong guess that there is no trailing spaces in company value as. it's just that when you say where company = 'Media Services '; it just ignores the trailing space and gives you the matched result.

So essentially, update never happens since there is nothing to update actually and so UPDATE says 0 rows affected.

Upvotes: 1

Flash Thunder
Flash Thunder

Reputation: 12036

The function that should work if select returns results, would be:

UPDATE table SET company = 'Media Services' WHERE id in (SELECT id from table WHERE company = 'Media Services ') ids ...

Of course if table has unique id field.

Upvotes: 0

Xavjer
Xavjer

Reputation: 9224

How about:

UPDATE table SET company = RTRIM(company)

This will remove all trailing spaces http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_rtrim

Upvotes: 0

Related Questions