程序師世界是廣大編程愛好者互助、分享、學習的平台,程序師世界有你更精彩!
首頁
編程語言
C語言|JAVA編程
Python編程
網頁編程
ASP編程|PHP編程
JSP編程
數據庫知識
MYSQL數據庫|SqlServer數據庫
Oracle數據庫|DB2數據庫
 程式師世界 >> 數據庫知識 >> MYSQL數據庫 >> MySQL綜合教程 >> 解決mysql修改表記錄1093錯誤

解決mysql修改表記錄1093錯誤

編輯:MySQL綜合教程

解決mysql修改表記錄1093錯誤   1.需求 : 已知表信息如下 : 表名 :frjg 表結構: [sql]  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.解決辦法: 錯誤實現方法: [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;      

  1. 上一頁:
  2. 下一頁:
Copyright © 程式師世界 All Rights Reserved