BrnDmp

Updating database field

Client wanted contents of db field to be exchanged.

So x y somepattern z z should become somepattern x y z z

Added column to the table

alter table prefix_user add tmp_username varchar(100);

alter table prefix_user add firstname_backup varchar(100);

now update tmp_username based on firstname

update prefix_user set firstname_backup = firstname where id = id;

select ltrim(concat_ws(‘ ‘,substring_index(firstname,’ ‘,-1),substring_index(firstname,’ ‘,1))) from prefix_user where firstname like ‘%pattern1%’ or firstname like ‘%pattern2%’ or firstname like ‘%pattern3%’;

becomes:

update prefix_user set tmp_username = ltrim(concat_ws(‘ ‘,substring_index(firstname,’ ‘,-1),substring_index(firstname,’pat1′,1))) where id = id and firstname like ‘%pat1%’;

update prefix_user set tmp_username = ltrim(concat_ws(‘ ‘,substring_index(firstname,’ ‘,-1),substring_index(firstname,’pat2′,1))) where id = id and firstname like ‘%pat2%’;

update prefix_user set tmp_username = ltrim(concat_ws(‘ ‘,substring_index(firstname,’ ‘,-1),substring_index(firstname,’pat3′,1))) where id = id and firstname like ‘%pat3%’;

If ok, update firstname based on tmp_username

update prefix_user set firstname = tmp_username where id = id and tmp_username IS NOT NULL;