-1

For example:

I have text in my column like: 'some text with word to replace' and i want to replace:

word with Word

i do:

update table set column = replace(column, 'word', 'Word');

and i get error:

Mysql: #1442 - Can't update table 'table' in stored function/trigger because it is already used by statement which invoked this stored function/trigger.

Vikdor
  • 23,934
  • 10
  • 61
  • 84
pain.reign
  • 371
  • 2
  • 4
  • 17

2 Answers2

1

If you want to change only the first letter :

UPDATE MyTable
SET myColumn = CONCAT(UCASE(LEFT(myColumn, 1)), SUBSTRING(myColumn, 2));

If you want to change all the column :

UPDATE MyTable
SET myColumn = UPPER(myColumn);

If you want to replace some words, you have to use the replace function :

UPDATE MyTable SET myColumn = replace(myColumn, 'word', 'Word');

Please to consider to accept my answer if it's OK for you.

EDIT : Adding a third example to search and replace a word in the field and replace it to another one.

K4timini
  • 711
  • 2
  • 14
  • 34
  • First example: 'Some text with word to replace' second example: 'SOME TEXT WITH WORD TO REPLACE' required: 'some text with Word to replace' do you see any difference? – pain.reign Apr 18 '14 at 17:38
  • My third example should do the trick. For example, it will replace all 'word' elements to 'Word' for each rows. – K4timini Apr 20 '14 at 11:54
  • Did you add this to a trigger ?! It seems MySQL doesn't allow to update in a update trigger due to infinite loop problem. – K4timini Apr 20 '14 at 12:05
  • Yeah i didn't add it to the trigger i will try it tomorrow and let you know – pain.reign Apr 21 '14 at 19:11
  • I didn't really test it but i suppose it would work with trigger. Just its out of scope for me now. So i cannot accept your answer because the query as you showed doesn't work. But i will +1 you for trigger hint. – pain.reign Apr 22 '14 at 15:22
0

Use UPPER or LOWER functions in mysql.

Anthony Horne
  • 2,522
  • 2
  • 29
  • 51
  • Can you give an example? if i use upper or lower it will replace whole line but i need to replace only certain word in line with uppercase first letter – pain.reign Apr 18 '14 at 14:43
  • I misunderstood the question, but if it is the message you are trying to fix, try putting sware brackets around the table and field names. – Anthony Horne Apr 18 '14 at 14:47