CODEWITHSUNDEEP
mysqlsql-update
timmalos
timmalos

Reputation: 542

MYSQL Change multiple columns

I got this table :

APPLICATION     TEST    DESCRIPTION     FICHIERCONF     FICHIERXML  FICHIERPROXY    PORTAIL     PERIODEMAINTENANCE  NOMDNS  TPSWARN     TPSCRIT     TYPEMAJ     TYPERAPPORT     CONTREMESURE    TPSCONTREMESURE     AUTH_URL    APP_URL_ENC
cerbere     Application-Ping    test    cete-config-cerbere     cerbere.xml     config-no-proxy.xml     0       cerbere.application.i2  0   0   1   Nagios  1   30  NULL    NULL
cerbere     Application-Ping2   test    cete-config-cerbere     cerbere.xml     config-no-proxy.xml     0       cerbere.application.i2  0   0   1   Nagios  1   30  NULL    NULL

For 1 single row I want to change 'cerbere' each time i see it by another text, in ALL columns.

For exemple i say : change 'cerbere' in the first row by 'lidia'. I want my application field to be changed by 'lidia', fichierconf field changed by 'cete-config-lidia', fichierxml by lidia.xml, nomdns by 'lidia.application.i2'.

Is there a way to do this?

Upvotes: 6

Views: 3935

Answers (1)

KennyBartMan
KennyBartMan

Reputation: 960

You need to use the update table syntax.

update 
TABLE_NAME 
set 
FIELD_NAME = replace(FIELD_NAME, 'find this string', 'replace found string with this string')
, ANOTHER_FIELD_NAME = REPLACE(ANOTHER_FIELD_NAME, 'foo', 'bar')
, AND_ANOTHER_ANOTHER_FIELD_NAME = REPLACE(...)
... /*as much columns as you like*/
;

You could do this for each column although there might be still a better solution this quick and dirty hack should get you started.

Upvotes: 7

Related Questions