Update Value in Table from another table

Question

I realized that i was using a varchar attribute as a index/key in a query, and that is killing my query performance. I am trying to look in my precienct table and get the integer ID, and then update my record in the household table with the new int FK, placed in a new column. this is the sql i have written thus far. but i am getting a

Error 1093 You can't specify target table 'voterfile_household' for update in FROM clause, and i am not sure how to fix it.

UPDATE voterfile_household
SET
PrecID = (SELECT voterfile_precienct.ID
        FROM voterfile_precienct INNER JOIN voterfile_household
        WHERE voterfile_precienct.PREC_ID = voterfile_household.Precnum);

Answer 1

Try:

update voterfile_household h, voterfile_precienct p
   set h.PrecID = p.ID
 where p.PREC_ID = h.Precnum

Take a look at update reference here.

Similarly, you can use inner join syntax as well.

update voterfile_household h inner join voterfile_precienct p on (h.Precnum = p.PREC_id)
   set h.PrecID = p.ID