解决mysql 修改表记录 1093错误

1.需求 ：

已知表信息如下 ：

表名 ：frjg

表结构：

DROP TABLE IF EXISTS `frjg`;
CREATE TABLE IF NOT EXISTS `frjg` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `jgbh` varchar(3) CHARACTER SET utf8 NOT NULL COMMENT '法人机构编号',
  `jgmc` varchar(50) CHARACTER SET utf8 NOT NULL COMMENT '机构名称',
  `jgm` varchar(8) CHARACTER SET utf8 NOT NULL COMMENT '机构码',
  `htqssj` date NOT NULL COMMENT '合同起始时间',
  `htyxsj` date NOT NULL COMMENT '合同有效时间',
  `dxfsl` int(11) NOT NULL COMMENT '短信发送总量',
  `dxsyl` int(11) NOT NULL COMMENT '短信剩余量',
  `mbkhl` int(11) NOT NULL COMMENT '目标客户量',
  `cjsj` datetime NOT NULL COMMENT '创建时间',
  `lastactive` datetime NOT NULL COMMENT '最后一次操作时间',
  `xmmc` varchar(50) CHARACTER SET utf8 NOT NULL COMMENT '项目名称',
  `cjr` varchar(50) CHARACTER SET utf8 DEFAULT NULL COMMENT '创建人',
  `state` int(11) NOT NULL DEFAULT '1' COMMENT '法人机构当前状态，0：正常，1：关闭',
  `weight` int(11) DEFAULT '0' COMMENT '权重，当生育量小时，判断分给谁',
  `spbz` int(11) DEFAULT '0' COMMENT '审批标准，当大于此标志时，任务挂起，短信通知',
  `jgjc` varchar(50) DEFAULT NULL COMMENT '机构简称',
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=gbk AUTO_INCREMENT=121 ;

--
-- 转存表中的数据 `frjg`
--

INSERT INTO `frjg` (`id`, `jgbh`, `jgmc`, `jgm`, `htqssj`, `htyxsj`, `dxfsl`, `dxsyl`, `mbkhl`, `cjsj`, `lastactive`, `xmmc`, `cjr`, `state`, `weight`, `spbz`, `jgjc`) VALUES
(1, '001', '省管理中心', '27000099', '2012-08-29', '2014-09-01', 200000, 200000, 10000, '2012-07-26 00:00:00', '2012-08-08 15:38:00', 'test2', 'zichen', 0, 0, 0, NULL),
(2, '002', '临渭区联社管理中心', '27050199', '2012-08-29', '2014-09-01', 200000, 199973, 10000, '2012-07-26 00:00:00', '2012-08-08 15:38:00', 'test2', 'zichen', 0, 0, 0, NULL),
(3, '003', '新城区联社管理中心', '27011399', '2012-08-29', '2014-09-01', 200000, 199954, 10000, '2012-07-26 00:00:00', '2012-08-08 15:38:00', 'test2', 'zichen', 0, 0, 0, NULL),
(4, '004', '碑林区联社管理中心', '27010199', '2012-08-29', '2014-09-01', 200000, 199977, 10000, '2012-07-26 00:00:00', '2012-08-08 15:38:00', 'test2', 'zichen', 0, 0, 0, NULL),
(5, '005', '灞桥联社管理中心', '27010499', '2012-08-29', '2014-09-01', 200000, 199975, 10000, '2012-07-26 00:00:00', '2012-08-08 15:38:00', 'test2', 'zichen', 0, 0, 0, NULL),
(6, '006', '雁塔联社管理中心', '27010299', '2012-08-29', '2014-09-01', 200000, 199871, 10000, '2012-07-26 00:00:00', '2012-08-08 15:38:00', 'test2', 'zichen', 0, 0, 0, NULL),
(7, '007', '未央区联社管理中心', '27010399', '2012-08-29', '2014-09-01', 200000, 199928, 10000, '2012-07-26 00:00:00', '2012-08-08 15:38:00', 'test2', 'zichen', 0, 0, 0, NULL),
(8, '008', '莲湖联社管理中心', '27010599', '2012-08-29', '2014-09-01', 200000, 199960, 10000, '2012-07-26 00:00:00', '2012-08-08 15:38:00', 'test2', 'zichen', 0, 0, 0, NULL),
(9, '009', '临潼联社管理中心', '27010699', '2012-08-29', '2014-09-01', 200000, 199990, 10000, '2012-07-26 00:00:00', '2012-08-08 15:38:00', 'test2', 'zichen', 0, 0, 0, NULL),
(10, '010', '西安市阎良区联社管理中心', '27010799', '2012-08-29', '2014-09-01', 200000, 199978, 10000, '2012-07-26 00:00:00', '2012-08-08 15:38:00', 'test2', 'zichen', 0, 0, 0, NULL),
(11, '011', '长安区信用联社管理中心', '27010899', '2012-08-29', '2014-09-01', 200000, 199956, 10000, '2012-07-26 00:00:00', '2012-08-08 15:38:00', 'test2', 'zichen', 0, 0, 0, NULL),
(12, '012', '周至联社管理中心', '27011099', '2012-08-29', '2014-09-01', 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.解决办法：

错误实现方法：

update frjg set frjg.jgjc=(select jgmc from frjg f where  f.id=frjg.id ) where  1

执行SQL命令，报错。错误信息为

#1093 - You can't specify target table 'frjg' for update in FROM clause

正确实现方法，需要建立一张中间表，中间表的内容和frjg表完全相同，之后进行修改frjg表，中间和frjg表之间存在“主外键关系”（并不存在这种关系）：

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;

 

 上边的方法是可以实现的，但对于上述问题，还有一种更简单的解决办法，一条sql语句搞定（上述方法可以误导了大家，灰常抱歉，现在改正错误） ：

   update frjg  SET jgjc = jgmc   WHERE 1;