MYSQL UPDATE query for multiple values

Question

Looking to update a table from values in another table. The tables are joined on a person's first and last name. The issue is we have duplicate records where we are trying to concatenate a value for each record, however when the query goes off it only finds the first record. Table1 and Table2 both contain around 20k records.

Current query

UPDATE table1, table2 SET table1.value = CONCAT(table1.pet,",",table2.pet) 
WHERE table1.fName = table2.fName AND table1.lName = table2.lName;

Table1 Example

fName  lName   pet
===================
John   Doe     bird
Jane   Doe     bird

Table2 Example

fName lName  pet
===================
John  Doe     dog
Jane  Doe     dog
John  Doe     fish
John  Doe     cat

Expected Results in Table1

fName lName  pet
===================
John  Doe     bird,dog,fish,cat
Jane  Doe     bird,dog

Results we are currently getting

fName lName  pet
===================
John  Doe     bird,dog
Jane  Doe     bird,dog

Any help is appreciated!

Fahmi · Accepted Answer · 2019-01-02 04:57:51Z

1

You can try below - using group_concat() function

update Table1 a
join
(
select fName,lName, group_concat(pet) as pet1
from Table2
group by fName,lName
)b on a.fName=b.fName and a.lName=b.lName
set a.pet=concat(a.pet,',',pet1)

answered Jan 2, 2019 at 4:57

Fahmi

37.5k5 gold badges26 silver badges32 bronze badges

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

1 Comment

Anirudha Gupta Over a year ago

group_concat have a text limit, If this was small on his server, this could cause a trouble.

Kandy · Accepted Answer · 2019-01-22 05:31:50Z

0

you can try this also

 UPDATE Table1  X SET 
X.Column1=Y.column2
from(
select Column2 from tabel2
where column3=column4 
) Y where X.column4=Y.column4

edited Jan 22, 2019 at 5:31

answered Jan 2, 2019 at 5:04

Kandy

6739 silver badges22 bronze badges

Collectives™ on Stack Overflow

MYSQL UPDATE query for multiple values

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related