mySQL update column with concat of another table

Question

experience> novice, 68/m

MySQL> 5.5.46-0+deb7u1

Database> apparel

There are two tables> femme PRIMARY = id, colors PRIMARY = c_id

I need the following table femme to be modified (or updated ...)

id      Name        Fav_Colors      City        

 1      Joe         red             Athens  

 1      Rea         grey            Rome

table colors must contribute it's data to table femme by replacing the above colors (flavored with some tags):

c_id    Name        Pref_Color                  City        City_color 

 1      Joe         yellow                      Athens      blue        

 2      Rea         green                       Rome        black

When femme is TRUNCATE(d) at the beginning everything is ok resulting the modified femme table bellow:

TRUNCATE TABLE `femme`;

REPLACE INTO `femme`(`id`, `name`, `Fav_Colors`, `City`)

SELECT
CONCAT ('

c_id,
Name,
<div>', colors.Pref_Color, ' - ', colors.City_color,'"</div>',
City
')
from colors;


id      Name        Fav_Colors                      City        

 1      Joe         <div>yellow - blue</div>        Athens  

 1      Rea         <div>green - black</div>        Rome

I just need to do it by replacing only the Fav_Colors column, but the following is not working:

REPLACE INTO `femme`(`Fav_Colors`) 
SELECT 
CONCAT ('
<div>', colors.Pref_Color, ' - ', colors.City_color,'"</div>'
')
from colors;

Would you please assist to find a way to resolve this issue? Thank you.

Could it be because of the double single quote after </div>? — Tomaso Albinoni
– Tomaso Albinoni, Commented Nov 18, 2015 at 2:56
Thank you Tomaso. You are right, the double single quote is not needed. It was missplaced during editing of this request, but it is only a typo error. My problem is that even with an empty CONCAT(' '), the specific field is not updated if the whole table is not TRUNCATE(d). I need to replace the contents of a single field (Fav_Colors) only and leave the rest of columns untuched. Sorry for my English, I'm a mediterranean (GR). — Ilias gr
– Ilias gr, Commented Nov 18, 2015 at 8:22

Tomaso Albinoni · Accepted Answer · 2015-11-18 20:07:57Z

2

Looks like you need a simple UPDATE. Does femme.id always correspond to colors.c_id? If so, you can do:

UPDATE femme
JOIN colors ON femme.id = colors.c_id
SET femme.Fav_Colors =
CONCAT('<div>', colors.Pref_Color, ' - ', colors.City_color,'</div>')

answered Nov 18, 2015 at 20:07

Tomaso Albinoni

1,0131 gold badge8 silver badges20 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Ilias gr Over a year ago

Thanks for your valuable assistance and the working solution given Tomaso :)

Tomaso Albinoni Over a year ago

You're welcome! Don't hesitate to accept the answer if it was helpful.

Collectives™ on Stack Overflow

mySQL update column with concat of another table

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related