Description:
Some UPDATES would be more convenient if the table being updated could be used in a subquery. MySQL could accomplish this by creating a temporary copy of the table and using the copy for the subquery. For instance, today I had a table part of which was the following:
mysql> select name, released, block, rotation_block from cardset where rotation_block is not null or block = '(core)' order by released;
+-------------------------+------------+-------------+----------------+
| name | released | block | rotation_block |
+-------------------------+------------+-------------+----------------+
| Limited Edition Alpha | 1993-08-05 | (core) | NULL |
| Limited Edition Beta | 1993-10-01 | (core) | NULL |
| Unlimited Edition | 1993-12-01 | (core) | NULL |
| Revised Edition | 1994-04-01 | (core) | NULL |
| Fourth Edition | 1995-04-01 | (core) | NULL |
| Ice Age | 1995-06-01 | Ice Age | Ice Age |
| Alliances | 1996-06-10 | Ice Age | Ice Age |
| Mirage | 1996-10-08 | Mirage | Mirage |
| Visions | 1997-02-03 | Mirage | Mirage |
| Fifth Edition | 1997-03-24 | (core) | NULL |
| Weatherlight | 1997-06-09 | Mirage | Mirage |
| Tempest | 1997-10-14 | Tempest | Tempest |
| Stronghold | 1998-03-02 | Tempest | Tempest |
| Exodus | 1998-06-15 | Tempest | Tempest |
| Urza's Saga | 1998-10-12 | Urza | Urza |
| Urza's Legacy | 1999-02-15 | Urza | Urza |
| Classic Sixth Edition | 1999-04-28 | (core) | NULL |
| Urza's Destiny | 1999-06-07 | Urza | Urza |
| Mercadian Masques | 1999-10-04 | Masques | Masques |
| Nemesis | 2000-02-14 | Masques | Masques |
| Prophecy | 2000-06-05 | Masques | Masques |
| Invasion | 2000-10-02 | Invasion | Invasion |
| Planeshift | 2001-02-05 | Invasion | Invasion |
| Seventh Edition | 2001-04-11 | (core) | NULL |
| Apocalypse | 2001-06-04 | Invasion | Invasion |
| Odyssey | 2001-10-01 | Odyssey | Odyssey |
| Torment | 2002-02-04 | Odyssey | Odyssey |
| Judgment | 2002-05-27 | Odyssey | Odyssey |
| Onslaught | 2002-10-07 | Onslaught | Onslaught |
| Legions | 2003-02-03 | Onslaught | Onslaught |
| Scourge | 2003-05-26 | Onslaught | Onslaught |
| Eighth Edition | 2003-07-28 | (core) | NULL |
| Mirrodin | 2003-10-02 | Mirrodin | Mirrodin |
| Darksteel | 2004-02-06 | Mirrodin | Mirrodin |
| Fifth Dawn | 2004-06-04 | Mirrodin | Mirrodin |
| Champions of Kamigawa | 2004-10-01 | Kamigawa | Kamigawa |
| Betrayers of Kamigawa | 2005-02-04 | Kamigawa | Kamigawa |
| Saviors of Kamigawa | 2005-06-03 | Kamigawa | Kamigawa |
| Ninth Edition | 2005-07-29 | (core) | NULL |
| Ravnica: City of Guilds | 2005-10-07 | Ravnica | Ravnica |
| Guildpact | 2006-02-03 | Ravnica | Ravnica |
| Dissension | 2006-05-05 | Ravnica | Ravnica |
| Coldsnap | 2006-07-21 | Ice Age | Time Spiral |
| Time Spiral | 2006-10-06 | Time Spiral | Time Spiral |
| Planar Chaos | 2007-02-02 | Time Spiral | Time Spiral |
| Future Sight | 2007-05-04 | Time Spiral | Time Spiral |
| Tenth Edition | 2007-07-14 | (core) | NULL |
| Lorwyn | 2007-10-12 | Lorwyn | Lorwyn |
| Morningtide | 2008-02-01 | Lorwyn | Lorwyn |
| Shadowmoor | 2008-05-02 | Shadowmoor | Lorwyn |
| Eventide | 2008-07-25 | Shadowmoor | Lorwyn |
| Shards of Alara | 2008-10-03 | Alara | Alara |
| Conflux | 2009-02-06 | Alara | Alara |
| Alara Reborn | 2009-04-30 | Alara | Alara |
| Magic 2010 | 2009-07-17 | (core) | Alara |
| Zendikar | 2009-10-02 | Zendikar | Zendikar |
+-------------------------+------------+-------------+----------------+
I wanted to change each NULL in rotation_block to the value above it if there was one.
How to repeat:
mysql> update cardset set rotation_block = (select rotation_block from cardset other where other.released <= cardset.released and rotation_block is not null order by other.released desc limit 1) where block = '(core)';
ERROR 1093 (HY000): You can't specify target table 'cardset' for update in FROM clause
Suggested fix:
MySQL could treat this as equivalent to the following, which is what I ended up doing:
create temporary table cardset2 like cardset;
insert into cardset2 select * from cardset;
update cardset set rotation_block = (select rotation_block from cardset2 where cardset2.released <= cardset.released and rotation_block is not null order by cardset2.released desc limit 1) where block = '(core)';
drop table cardset2;
