CODEWITHSUNDEEP
mysql
cjones
cjones

Reputation: 19

MYSQL UPDATE query for multiple values

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!

Upvotes: 1

Views: 34

Answers (2)

Kandy
Kandy

Reputation: 673

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

Upvotes: 0

Fahmi
Fahmi

Reputation: 37493

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)

Upvotes: 1

Related Questions