Replace data in MySQL table with data from another table

Question

I am trying to modify an existing MySQL database for use in a new application. I have a table of items (table_items), which has multiple fields, including "ItemID" and "ItemName". I have another table (table_list) which has "ItemName" in it, but no ItemID. I need to either update this table to contain ItemID instead of ItemName, or create a new table which imports ItemIDs from table_items as opposed to the ItemName when table_list.ItemName = table_items.ItemName.

I have tried the following: UPDATE table_list A, table_items B SET A.ItemName = B.ItemID WHERE A.ItemName = B.ItemName

The current table has over 500,000 rows and every time i try this in PHPMyAdmin i get the error "the MySQl server has gone away".

Any help greatly appreciated.

Answer 1

Stop the mysql service, locate the file my.cnf in /etc (/etc/my.cnf mysql directory), open it and find the variable max_allowed_packet that is probably set to 1MB, change it to fit your needs.

Also, instead of this:

UPDATE table_list A, table_items B SET A.ItemName = B.ItemID WHERE A.ItemName = B.ItemName

try this:

UPDATE table_list A SET ItemName = (SELECT ItemID FROM table_items B WHERE A.ItemName = B.ItemID);