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.
Best Answer
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: