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;
`termId` int(11) NOT NULL auto_increment,
`termName` varchar(50) NOT NULL default '',
`categoryId` int(11) NOT NULL,
PRIMARY KEY (`termId`)
) TYPE=InnoDB;
因为具体操作的人不太懂这个东西,复杂的操作不来。update report,manageTerm set reportTo=termId where ???
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');
结果如下: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
這句是可行並且證明是正確的,樓主可參照使用。
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)
你的好像有错误吧?测试通过???自己脑子里想着通过的不要拿出来哈。:) 子句里至少应该有
.....) x ,report where ....
你可以不看下面的东西,不过你要回答问题先看看问题描述。谢谢!
(
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 t
FROM re_category_admin r, manageterm
(
select adminId a,termId t from re_category_admin r,manageterm m
where r.categoryId=m.categoryId group by adminId
) x ,report
where x.a=report.reportTo;
以上语句都尝试了。
你的问题主要是出在这里。
这个问题的关键在于select结果可否像临时表那样看待,而你这里显然错的地方就是update set xxx=
后面跟的是结果集,并非单一结果。
---------------------------------------------------------------
-- 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 changed
mysql> 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)=========================================
ok,谢谢了,看来我需要另想办法
我是用你给的语句建的表和插入的数据,不过phpMyAdmin导出的时候改掉了。你给出的error信息还是select的,
不是update的
from report,(select adminId a,termId t from r,m
where r.categoryId=m.categoryId group by adminId
) x
where x.a=report.reportTo
----------------------
第2,3列分别是原来的和将要修改成的reportTo+----+----------+-----+---+
| id | reportTo | t | a |
+----+----------+-----+---+
| 1 | 1 | 101 | 1 |
| 2 | 1 | 101 | 1 |
| 5 | 1 | 101 | 1 |
| 3 | 2 | 102 | 2 |
| 6 | 2 | 102 | 2 |
| 4 | 3 | 103 | 3 |
+----+----------+-----+---+
6 rows in set (0.00 sec)------------------------
4.1 支持PreparedStatement (预处理语句,一种临时存储过程)如此而已
1 创建临时表,存储对应关系,
2 update tablea as a,talbeb as b set a.xxx = b.xxx where a.yyy=b.yyy;