1

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 );
user3529701
  • 17
  • 6

1 Answers1

0

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
Mureinik
  • 297,002
  • 52
  • 306
  • 350
  • what is "h" and I am receiving an error "near 'ORDER BY voterhistory SEPARATOR ',') AS history FR' at line 3 " – user3529701 Apr 25 '14 at 18:18
  • I had an extra whitespace after `group_concat` - fixed. To your question - `h` is the alias I gave the aggregate query in order to perform the `join`. – Mureinik Apr 25 '14 at 18:33
  • Alright. The query seems to be working, but is taking a while to complete. Is that expected? There are over 400k rows in the week2 and vhistory tables. – user3529701 Apr 25 '14 at 18:56
  • Anything you do on a table with 400K is bound to take some time - especially as you're updating all the rows here... are the `voternum` columns in both tables indexed? – Mureinik Apr 25 '14 at 19:00
  • The query completed eventually. Thanks a lot!! – user3529701 Apr 27 '14 at 05:36