How to update the value of a column by substituting in all rows of the table?

Asked

Viewed 58 times

4

I have a table i18n in the database whose column constant contains all values with prefix lang:

Description of the table

┌──────────────┬──────────────┬──────┬────────┬─────────────────────┬────────────────┐
│ Field        │ Type         │ Null │ Key    │ Default             │ Extra          │      
├──────────────┼──────────────┼──────┼────────┼─────────────────────┼────────────────┤
│ id           │ int(13)      │ NO   │ PRI    │ NULL                │ auto_increment │ 
├──────────────┼──────────────┼──────┼────────┼─────────────────────┼────────────────┤
│ type_id      │ int(13)      │ NO   │        │ NULL                │                │ 
├──────────────┼──────────────┼──────┼────────┼─────────────────────┼────────────────┤
│ i18n_id      │ varchar(3)   │ NO   │        │ NULL                │                │ 
├──────────────┼──────────────┼──────┼────────┼─────────────────────┼────────────────┤
│ constant     │ varchar(255) │ NO   │        │ NULL                │                │ 
├──────────────┼──────────────┼──────┼────────┼─────────────────────┼────────────────┤
│ value        │ tinytext     │ NO   │        │ NULL                │                │ 
├──────────────┼──────────────┼──────┼────────┼─────────────────────┼────────────────┤
│ description  │ varchar(255) │ NO   │        │ NULL                │                │ 
├──────────────┼──────────────┼──────┼────────┼─────────────────────┼────────────────┤
│ date_created │ timestamp    │ NO   │        │ 0000-00-00 00:00:00 │                │
├──────────────┼──────────────┼──────┼────────┼─────────────────────┼────────────────┤
│ date_updated │ timestamp    │ NO   │        │ CURRENT_TIMESTAMP   │                │
└──────────────┴──────────────┴──────┴────────┴─────────────────────┴────────────────┘

Content example

Example of content in column constant:

LANG_WORD_JOHN
LANG_WORD_DOE

What I am trying to do is update all the records in that table by changing the prefix LANG for I18N in the contents of the column constant, but so far without success!

I18N_WORD_JOHN
I18N_WORD_DOE

Question

How can I update the column value constant in all existing rows in the table by replacing the prefix LANG for I18N ?

mysql sql-update

1 answer

3


It is possible to perform the operation in a simple way using the REPLACE , which is part of the Mysql text manipulation functions:

UPDATE i18n SET constant = REPLACE(constant, 'LANG_', 'I18N_');

Browser other questions tagged mysql sql-update

You are not signed in. Login or sign up in order to post.