Changing values only in the last part.

[joon]

[b]myTable22 in mySQL4.0[/b]

[COLOR=Blue](id) name[/COLOR]
(1) 1234Tom
(2) 5678Jane
(3) 9012[COLOR=Red]Ted[/COLOR]
(4) 3456John
(5) 7890[COLOR=Red]Ted[/COLOR]

I have myTable22 like the above.

The following trial code produces the following result, but I like to produces my target result above.

[b]code[/b]

update myTable22
set name=left(name,4)[B][COLOR=Red]+'Mary'[/COLOR][/B]
where substring(name from 5)='Ted'

[b]result[/b]

(1) 1234Tom
(2) 5678Jane
(3) 9012
(4) 3456John
(5) 7890

[b]target result[/b]

(1) 1234Tom
(2) 5678Jane
(3) 9012[COLOR=Red]Mary[/COLOR]
(4) 3456John
(5) 7890[COLOR=Red]Mary[/COLOR]

Thanks in Advance

[devinemke]

UPDATE myTable22 SET name = CONCAT(SUBSTRING(name, 1, 4), 'Mary') WHERE SUBSTRING(name, 5) = 'Ted'