Updating rows from other rows in a huge table I have a huge table with items

item-id | name | type | extra


Many of them are "old versions" of type 0.

I have to update the extra field in all new items (same item-id, but type > 0) with the extra data we have for the old version.

Since this is a very large table with millions of rows, I have to be very careful with joining the table with itself.

Basically I want to do this

update mytable t1 set extra = (select extra from mytable where t1.itemid = mytable.itemid and type=0) where type &gt; 0;


But MySQL doesn't accept t1 in the sub-select.
Recently read an article that might help you, see: https://dzone.com/articles/specifying-target-tables-in-mysql

Still think a self join would be faster than a correlated sub-query. Especially on MySQL 5.5 and less, where these type of sub queries are badly optimized. 

Self join like:

UPDATE mytable t1
  JOIN mytable t2
    ON t1.item-id = t2.item-id
SET t1.extra = t2.extra
WHERE t1.type &gt; 0;