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 > 0;
But MySQL doesn't accept t1 in the sub-select.