CODEWITHSUNDEEP
mysql
Ihidan
Ihidan

Reputation: 419

Updating Column with other column value

Given the following table

id   name             Date
1    Hello_world_@13  15/05/2016
2    Bye_world_@22    16/07/2016
3    Random_Name_@75  18/09/2016

I need to update name column inserting the date value into it. I need to insert the date before the "@" and adding a "_" at the end of the date.

id   name                        Date
1    Hello_world_15/05/2016_@13  15/05/2016
2    Bye_world_16/07/2016_@22    16/07/2016
3    Random_Name_18/09/2016_@75  18/09/2016

Any ideas on queries that would do that?

Upvotes: 0

Views: 28

Answers (2)

Bernd Buffen
Bernd Buffen

Reputation: 15057

You can do it with REPLACE an CONCAT:

on your table use this where you must concat the date and the @

UPDATE yourTable SET NAME = REPLACE(`NAME`, '@' , CONCAT(`DATE`,'_@'));

sample

MariaDB []> SELECT REPLACE('Hello_world_@13', '@' , '15/05/2016_@');
+-------------------------------------------------+
| REPLACE('Hello_world_@13', '@' , '15/05/2016_@') |
+-------------------------------------------------+
| Hello_world_15/05/2016_@13                       |
+-------------------------------------------------+
1 row in set (0.00 sec)

MariaDB []>

Upvotes: 1

ScaisEdge
ScaisEdge

Reputation: 133360

Use replace

If the date column is already a string then use 

update table 
set name = replace(name, '@' , concat(date, '_@'));

else use date_format too

update table 
set name = replace(name, '@' , concat(DATE_FORMAT(date, '%d/%m/%Y'), '_@'));

Upvotes: 1

Related Questions