[SOLVED] UPDATE table using CONCAT

UPDATE table using CONCAT_WS error

I have 2 tables named vhistory and week2. vhistory includes two columns voternum and voterhistory. week2 contains columns age, address, voternum, voterhistory, status, gender, precinct, county, and zip5. I need to update week2 voterhistory using table vhistory where week2 voternum = vhistory voternum.

For example:
Within week2 there is a person with voternum = 1234. Within vhistory there are 3 records where voternum = 1234. One has a voterhistory = 2011, one has a voterhistory = 2012, and one has a voterhistory = 2013. This means when updating week2 the 3 records of vhistory should import into the voterhistory column of the week2 table like so: 2011, 2012, 2013.

This is what I have so far.

UPDATE week2 SET voterhistory=SELECT CONCAT_WS(',',SELECT voterhistory FROM vhistory
WHERE week2.voternum = vhistory.voternum );

Solution

I think an update-join statement is what you're looking for:

UPDATE week2 
JOIN   (SELECT   voternum, 
                 GROUP_CONCAT(voterhistory ORDER BY voterhistory SEPARATOR ',') 
                   AS history
        FROM     vhistory
        GROUP BY voternum) h ON week2.voternum = h.voternum
SET    voterhistory = history