My code runs but does not work ?

[Topshed]

mySQL ver: 5.1.30

PHP  ver: 5.2.8

 

I have a piece of code that completes but does nothing.

 

dbase: buses

Table : st

 

Fields used in the code

Seats: CHR (

Body:  CHR (22)

UPDATE st SET seats = REPLACE(seats, 'H29/20R ', 'H25/27RO') WHERE body LIKE 'Thomas Tilling'

 

Records 1140

Records to be changed 300

 

As I hope you can see I am trying to replace  'H29/20R ' in seats  WHEN  'Thomas Tilling' occurs in the body field

 

You help would be  much appreciated

[Muddy_Funster]

don't use REPLACE in an UPDATE query, it's only for SELECT.  Just include the value that you want the field to contain when you have run the UPDATE:

UPDATE st SET seats = 'H25/27RO' WHERE body LIKE 'Thomas Tilling'

 

As an afterthought, if you know the exact contnent of the field that you are looking up in the WHERE clause, use = instead of LIKE, it's more efficient:

UPDATE st SET seats = 'H25/27RO' WHERE body = 'Thomas Tilling'

[Topshed]

Thank you very much that fixed it with ease.

 

Trouble for me is When  I google "mySQL + REPLACE"  every simple looking answer comes up with

UPDATE st SET seats = REPLACE(seats, 'H29/20R ', 'H25/27RO' ) WHERE body LIKE 'Thomas Tilling';

 

And the even simpler (I assume) global replace turns up

UPDATE st set seats = REPLACE(seats, 'H29/20R' , '(H27/25RO' )

 

Both you will notice use UPDATE and REPLACE in the queries, no wonder I hit so many hurdles when trying to find answers

If you could show me the correct syntax for a global replace that would also help a lot.

 

Regards  Topshed

[Muddy_Funster]

the only thing that dictates which fields in the column are changed is the WHERE clause.  if there is no WHERE clause then all records will have their value updated in the column that you SET, so to perform a "global replace" as you call it simply leave off the WHERE.

UPDATE st SET seats = 'H25/27RO'

[Topshed]

Thank so much for Un-Muddying my thinking on this, a little basic knowledge goes a long way

 

Kind Regards

Topshed