解决mysql修改表记录1093错误(二)
1', 200000, 199995, 10000, '2012-07-26 00:00:00', '2012-08-08 15:38:00', 'test2', 'zichen', 0, 0, 0, NULL),
(13, '013', '高陵联社管理中心', '27011199', '2012-08-29', '2014-09-01', 200000, 199985, 10000, '2012-07-26 00:00:00', '2012-08-08 15:38:00', 'test2', 'zichen', 0, 0, 0, NULL),
(14, '014', '蓝田联社管理中心', '27011299', '2012-08-29', '2014-09-01', 200000, 199949, 10000, '2012-07-26 00:00:00', '2012-08-08 15:38:00', 'test2', 'zichen', 0, 0, 0, NULL),
(15, '015', '户县管理中心', '27010999', '2012-08-29', '2014-09-01', 200000, 199987, 10000, '2012-07-26 00:00:00', '2012-08-08 15:38:00', 'test2', 'zichen', 0, 0, 0, NULL),
(16, '016', '金台区信用联社管理中心', '27030299', '2012-08-29', '2014-09-01', 200000, 199957, 10000, '2012-07-26 00:00:00', '2012-08-08 15:38:00', 'test2', 'zichen', 0, 0, 0, NULL),
(17, '017', '渭滨信用联社管理中心', '27030199', '2012-08-29', '2014-09-01', 200000, 199981, 10000, '2012-07-26 00:00:00', '2012-08-08 15:38:00', 'test2', 'zichen', 0, 0, 0, NULL),
(18, '018', '咸阳市渭城区联社管理中心', '27040299', '2012-08-29', '2014-09-01', 200000, 199987, 10000, '2012-07-26 00:00:00', '2012-08-08 15:38:00', 'test2', 'zichen', 0, 0, 0, NULL),
(19, '019', '咸阳市秦都区联社管理中心', '27040199', '2012-08-29', '2014-09-01', 200000, 199979, 10000, '2012-07-26 00:00:00', '2012-08-08 15:38:00', 'test2', 'zichen', 0, 0, 0, NULL);
需求如下：需要将表记录中各自的 jgmc 字段值赋给各自的 jgjc
2.解决办法：
错误实现方法：
[sql]
update frjg set frjg.jgjc=(select jgmc from frjg f where f.id=frjg.id ) where 1
执行SQL命令，报错。错误信息为
[sql]
#1093 - You can't specify target table 'frjg' for update in FROM clause
正确实现方法，需要建立一张中间表，中间表的内容和frjg表完全相同，之后进行修改frjg表，中间和frjg表之间存在“主外键关系”（并不存在这种关系）：
[sql]
create table tmp as (select * FROM frjg WHERE 1 );
update frjg SET jgjc = (SELECT jgmc FROM tmp WHERE frjg.id=tmp.id ) WHERE 1;
drop table tmp;