高分求一sql语句，关于3表联合update（只修改其中之一）

高分求一sql语句，关于3表联合update（只修改其中之一）表结构如下：CREATE TABLE `report` ( `id` int(11) NOT NULL auto_increment, `title` varchar(50) NOT NULL default '', `content` text, `createTime` datetime default NULL, `reportTo` varchar(20) default NULL, PRIMARY KEY (`id`)) TYPE=InnoDB;CREATE TABLE `re_category_admin` ( `id` int(11) NOT NULL auto_increment, `categoryId` int(11) NOT NULL, `adminId` int(11) NOT NULL, PRIMARY KEY (`id`)) TYPE=InnoDB;CREATE TABLE `manageTerm` ( `termId` int(11) NOT NULL auto_increment, `termName` varchar(50) NOT NULL default '', `categoryId` int(11) NOT NULL, PRIMARY KEY (`id`)) TYPE=InnoDB;也就是有一个汇报表，一个板块-管理员对应表，一个管理团队表。说明：一个管理员可以属于多个管理团队，一个管理团队只数据一个板块。初期的时候，reportTo字段存的是adminId即管理员ID，现在需要存termId团队ID。这样出现了一个问题：对于以前的数据怎么样进行转换？状况是这样子的，数据比较完整，不会有关联到NULL的，但是一个管理员对应多个管理团队，这个时候任意选择一个管理团队就行。（因为report数据表缺来源字段，所以只能这样做了。。。）CREATE TABLE `manageTerm` ( `termId` int(11) NOT NULL auto_increment, `termName` varchar(50) NOT NULL default '', `categoryId` int(11) NOT NULL, PRIMARY KEY (`termId`)) TYPE=InnoDB;希望有人可以用一条sql语句完成。因为具体操作的人不太懂这个东西，复杂的操作不来。update report，manageTerm set reportTo=termId where ？？？UPDATE 能多表关联吗？好像不行吧。可以，没问题CREATE TABLE `testa` ( `id` int(11) NOT NULL auto_increment, `testId` int(11) NOT NULL, `numa` int(11) NOT NULL, PRIMARY KEY (`id`)) TYPE=InnoDB;CREATE TABLE `testb` ( `id` int(11) NOT NULL auto_increment, `testId` int(11) NOT NULL, `numb` int(11) NOT NULL, PRIMARY KEY (`id`)) TYPE=InnoDB;update testa,testb set numa=numb where testa.testId = testb.testId;大家可以用这个测试下。有兴趣的帮忙顶啊:)多表是可以，不过你的意思说的还是不够清楚。因为这些表都是简化的基本字段，所以大家不要想其他的，就从这些必要字段中考虑吧。如果有人能确切的说办不到请给讲解下:)......老大......算了,我再来编点数据吧......CREATE TABLE `report` ( `id` int(11) NOT NULL auto_increment, `title` varchar(50) NOT NULL default '', `content` text, `createTime` datetime default NULL, `reportTo` varchar(20) default NULL, PRIMARY KEY (`id`)) TYPE=InnoDB;INSERT INTO report VALUES (1,'测试1','测试内容1','2005-04-16 15:00:00','1');INSERT INTO report VALUES (2,'测试2','测试内容2','2005-04-16 15:00:00','1');INSERT INTO report VALUES (3,'测试3','测试内容3','2005-04-16 15:00:00','2');INSERT INTO report VALUES (4,'测试4','测试内容4','2005-04-16 15:00:00','3');INSERT INTO report VALUES (5,'测试5','测试内容5','2005-04-16 15:00:00','1');INSERT INTO report VALUES (6,'测试6','测试内容6','2005-04-16 15:00:00','2');CREATE TABLE `re_category_admin` ( `id` int(11) NOT NULL auto_increment, `categoryId` int(11) NOT NULL, `adminId` int(11) NOT NULL, PRIMARY KEY (`id`)) TYPE=InnoDB;INSERT INTO re_category_admin VALUES (1,'1','1');INSERT INTO re_category_admin VALUES (2,'2','1');INSERT INTO re_category_admin VALUES (3,'3','1');INSERT INTO re_category_admin VALUES (4,'2','2');INSERT INTO re_category_admin VALUES (5,'3','2');INSERT INTO re_category_admin VALUES (6,'3','3');CREATE TABLE `manageTerm` ( `termId` int(11) NOT NULL auto_increment, `termName` varchar(50) NOT NULL default '', `categoryId` int(11) NOT NULL, PRIMARY KEY (`id`)) TYPE=InnoDB;INSERT INTO manageTerm VALUES (101,'管理团队1','1');INSERT INTO manageTerm VALUES (102,'管理团队2','2');INSERT INTO manageTerm VALUES (103,'管理团队3','3');我希望一句update以后report中所有数据的report字段被更新结果如下:INSERT INTO report VALUES (1,'测试1','测试内容1','2005-04-16 15:00:00','in(101,102,103)');INSERT INTO report VALUES (2,'测试2','测试内容2','2005-04-16 15:00:00','in(101,102,103)');INSERT INTO report VALUES (3,'测试3','测试内容3','2005-04-16 15:00:00','in(102,103)');INSERT INTO report VALUES (4,'测试4','测试内容4','2005-04-16 15:00:00','in(103)');INSERT INTO report VALUES (5,'测试5','测试内容5','2005-04-16 15:00:00','in(101,102,103)');INSERT INTO report VALUES (6,'测试6','测试内容6','2005-04-16 15:00:00','in(102,103)');注：in(xxx,xxx......) 的意思是更新后这个字段的值可以是其中任何一个。例如：INSERT INTO report VALUES (1,'测试1','测试内容1','2005-04-16 15:00:00',‘101');INSERT INTO report VALUES (2,'测试2','测试内容2','2005-04-16 15:00:00','101');INSERT INTO report VALUES (3,'测试3','测试内容3','2005-04-16 15:00:00','102'); INSERT INTO report VALUES (4,'测试4','测试内容4','2005-04-16 15:00:00','103');INSERT INTO report VALUES (5,'测试5','测试内容5','2005-04-16 15:00:00','101');INSERT INTO report VALUES (6,'测试6','测试内容6','2005-04-16 15:00:00','102');或者INSERT INTO report VALUES (1,'测试1','测试内容1','2005-04-16 15:00:00',‘101');INSERT INTO report VALUES (2,'测试2','测试内容2','2005-04-16 15:00:00','102');INSERT INTO report VALUES (3,'测试3','测试内容3','2005-04-16 15:00:00','102');INSERT INTO report VALUES (4,'测试4','测试内容4','2005-04-16 15:00:00','103');INSERT INTO report VALUES (5,'测试5','测试内容5','2005-04-16 15:00:00','101');INSERT INTO report VALUES (6,'测试6','测试内容6','2005-04-16 15:00:00','102');.......等等很多，只要能是其中一种就行。:)这个例子大家不用仔细看（只是怕产生误解才写的）仔细看看我前面的说明就行了，很明白了已经。。。。看那些很頭疼，給你一個看有沒有用update a set a.dp='w' left join b on a.id=b.id left join c on a.na=c.na where a.rec>0這句是可行並且證明是正確的，樓主可參照使用。markTYPE=InnoDB新鲜。这样居然就可以了？强。答案在此，得到你的第一个答案（改成别的答案也可以，改中间那个子查询就可以）4。1上运行通过（你的re_category_admin表名我改成了r，manageTerm改成了m）update report set reportTo=( select t from (select adminId a,termId t from r,m where r.categoryId=m.categoryId group by adminId) x where x.a=report.reportTo)to helloyou0(你好！) :你的好像有错误吧？测试通过？？？自己脑子里想着通过的不要拿出来哈。:) 子句里至少应该有.....) x ,report where ....to zalvsa(zalvsa) :你可以不看下面的东西，不过你要回答问题先看看问题描述。谢谢！我晕，你试了没有，如果有错可以拿出来看啊我回答问题倒是认真测试，问问题的反而不试恩。测试过了。有错误。我的mysql 是4.0的，不过我想3.以上的都没问题吧我上面的表和数据能麻烦你在本地创建一下么？然后执行下:)我是4。1，已经通过。你可以把出错的信息拿出来分析一下select t from (select adminId a,termId t from re_category_admin r,manageterm m where r.categoryId=m.categoryId group by adminId) x where x.a=report.reportTo;#1064 - You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT adminId a, termId tFROM re_category_admin r, managetermselect t from (select adminId a,termId t from re_category_admin r,manageterm m where r.categoryId=m.categoryId group by adminId) x ,reportwhere x.a=report.reportTo;我晕，这个语句是不好这样测试的。你连t是谁的同名都没指出来。你直接试我的语句。说错了，你的语句里report不在from里，当然不对？？？你的那个我早就试过了，结果一样。我是说你这里有问题。以上语句都尝试了。你的问题主要是出在这里。这个问题的关键在于select结果可否像临时表那样看待，而你这里显然错的地方就是update set xxx=后面跟的是结果集，并非单一结果。如果你那个真的行，你把测试数据表结构和数据导出来，然后把完整sql语句贴出来，我测试下，谢谢运行前数据：----------------------------------------------------------------- phpMyAdmin SQL Dump-- version 2.6.1-- http://www.phpmyadmin.net-- -- Host: localhost-- Generation Time: Apr 17, 2005 at 03:11 AM-- Server version: 4.1.10-- PHP Version: 4.3.9-- -- Database: `test`-- -- ---------------------------------------------------------- -- Table structure for table `m`-- DROP TABLE IF EXISTS m;CREATE TABLE m ( termId int(11) NOT NULL auto_increment, termName varchar(50) NOT NULL default '', categoryId int(11) NOT NULL default '0', PRIMARY KEY (termId)) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=104 ;-- -- Dumping data for table `m`-- INSERT INTO m VALUES (101, '1¨¹¨¤¨ª¨ª??¨®1', 1);INSERT INTO m VALUES (102, '1¨¹¨¤¨ª¨ª??¨®2', 2);INSERT INTO m VALUES (103, '1¨¹¨¤¨ª¨ª??¨®3', 3);-- ---------------------------------------------------------- -- Table structure for table `r`-- DROP TABLE IF EXISTS r;CREATE TABLE r ( id int(11) NOT NULL auto_increment, categoryId int(11) NOT NULL default '0', adminId int(11) NOT NULL default '0', PRIMARY KEY (id)) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=7 ;-- -- Dumping data for table `r`-- INSERT INTO r VALUES (1, 1, 1);INSERT INTO r VALUES (2, 2, 1);INSERT INTO r VALUES (3, 3, 1);INSERT INTO r VALUES (4, 2, 2);INSERT INTO r VALUES (5, 3, 2);INSERT INTO r VALUES (6, 3, 3);-- -------------------------------------------------------- -- -- Table structure for table `report`-- DROP TABLE IF EXISTS report;CREATE TABLE report ( id int(11) NOT NULL auto_increment, title varchar(50) NOT NULL default '', content text, createTime datetime default NULL, reportTo varchar(20) default NULL, PRIMARY KEY (id)) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=7 ;-- -- Dumping data for table `report`-- INSERT INTO report VALUES (1, '??1', '????1', '2005-04-16 15:00:00', '1');INSERT INTO report VALUES (2, '??2', '????2', '2005-04-16 15:00:00', '1');INSERT INTO report VALUES (3, '??3', '????3', '2005-04-16 15:00:00', '2');INSERT INTO report VALUES (4, '??4', '????4', '2005-04-16 15:00:00', '3');INSERT INTO report VALUES (5, '??5', '????5', '2005-04-16 15:00:00', '1');INSERT INTO report VALUES (6, '??6', '????6', '2005-04-16 15:00:00', '2');＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝mysql中运行画面拷贝：mysql> use test;Database changedmysql> select * from report; ---- ------- --------- --------------------- ---------- | id | title | content | createTime | reportTo | ---- ------- --------- --------------------- ---------- | 1 | ??1 | ????1 | 2005-04-16 15:00:00 | 1 || 2 | ??2 | ????2 | 2005-04-16 15:00:00 | 1 || 3 | ??3 | ????3 | 2005-04-16 15:00:00 | 2 || 4 | ??4 | ????4 | 2005-04-16 15:00:00 | 3 || 5 | ??5 | ????5 | 2005-04-16 15:00:00 | 1 || 6 | ??6 | ????6 | 2005-04-16 15:00:00 | 2 | ---- ------- --------- --------------------- ---------- 6 rows in set (0.00 sec)mysql> update report set reportTo=( -> select t from -> ( -> select adminId a,termId t from r,m -> where r.categoryId=m.categoryId group by adminId -> ) x -> where x.a=report.reportTo);Query OK, 6 rows affected (0.03 sec)Rows matched: 6 Changed: 6 Warnings: 0mysql> select * from report; ---- ------- --------- --------------------- ---------- | id | title | content | createTime | reportTo | ---- ------- --------- --------------------- ---------- | 1 | ??1 | ????1 | 2005-04-16 15:00:00 | 101 || 2 | ??2 | ????2 | 2005-04-16 15:00:00 | 101 || 3 | ??3 | ????3 | 2005-04-16 15:00:00 | 102 || 4 | ??4 | ????4 | 2005-04-16 15:00:00 | 103 || 5 | ??5 | ????5 | 2005-04-16 15:00:00 | 101 || 6 | ??6 | ????6 | 2005-04-16 15:00:00 | 102 | ---- ------- --------- --------------------- ---------- 6 rows in set (0.00 sec)mysql>＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝另update后面并非结果集，因为有where x.a=report.reportTo这个约束条件。select t from (select adminId a,termId t from r,m where r.categoryId=m.categoryId group by adminId) x where x.a=report.reportTo)＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝