msg_info表里面有 id name type body time 字段
msg_read 表里有 msg_id(msg_info表的外键) uid(user表的外键) status(是否状态)
我想在插入 msg_info表 数据的时候 同时查出 type字段符合的用户 并且批量的插入到 msg_read 表中
请问有什么好办法么?
insert into msg_read(msg_id,uid,status) values (select id from msg_info where cretatetime='2009-06-17 12:00:27' as msgid,select id from sys_users where rightes='12','1')
这么写 也不好用啊 请大家帮帮忙啊!
msg_read 表里有 msg_id(msg_info表的外键) uid(user表的外键) status(是否状态)
我想在插入 msg_info表 数据的时候 同时查出 type字段符合的用户 并且批量的插入到 msg_read 表中
请问有什么好办法么?
insert into msg_read(msg_id,uid,status) values (select id from msg_info where cretatetime='2009-06-17 12:00:27' as msgid,select id from sys_users where rightes='12','1')
这么写 也不好用啊 请大家帮帮忙啊!
解决方案 »
- MySql服务挂掉
- mysql中,怎么查询一个表的主键都是哪些表调用了?或者都是哪些表做为外键了?
- mysql in字段需要建立索引么
- mysql c api 处理事务
- 请问mysql字段名,若是系统关键字,比如MOD,如何转义,象sqlserver一样加[],但是mysql加[]好象不行
- 请问MySql的linux下c语言开发包在哪里有下载?
- id为Autoincreament问题
- MySQL数据库使用何种JDBC DRIVER比较好?
- 高分求教,MyODBC!!!!!!!!!!!!!!!!!!
- mysql yum安装出错,搞了一天了,头疼!
- mysql数据导入问题
- mysql数据库的查询,大手们进来帮帮我啊。。困扰好几天了。
请问有什么好办法么?---------------------------
你这个也可以考虑用触发器来处理
恩 具体数字是 type 应该 是 1 rightes应该是 1,2,3
create table tb1(
-> gid int auto_increment primary key,
-> color int,
-> size int
-> );
Query OK, 0 rows affected (0.16 sec)mysql>
mysql> create table tb2(
-> id int auto_increment primary key,
-> gid int,
-> weight int
-> );
Query OK, 0 rows affected (0.05 sec)mysql>
mysql> create table tb3(
-> id int auto_increment primary key,
-> gid int,
-> c3 int
-> );
Query OK, 0 rows affected (0.08 sec)mysql> delimiter |
mysql>
mysql> CREATE TRIGGER t_tb1_ai AFTER INSERT ON tb1
-> FOR EACH ROW BEGIN
-> insert into tb2(gid) values (new.gid);
-> insert into tb3(gid) values (new.gid);
-> END;
-> |
Query OK, 0 rows affected (0.11 sec)mysql>
mysql> delimiter ;
mysql>
mysql> insert into tb1 (color) values (123);
Query OK, 1 row affected (0.27 sec)mysql> select * from tb1;
+-----+-------+------+
| gid | color | size |
+-----+-------+------+
| 1 | 123 | NULL |
+-----+-------+------+
1 row in set (0.00 sec)mysql> select * from tb2;
+----+------+--------+
| id | gid | weight |
+----+------+--------+
| 1 | 1 | NULL |
+----+------+--------+
1 row in set (0.00 sec)mysql> select * from tb3;
+----+------+------+
| id | gid | c3 |
+----+------+------+
| 1 | 1 | NULL |
+----+------+------+
1 row in set (0.00 sec)
CREATE TABLE test2(a2 INT);
CREATE TABLE test3(a3 INT NOT NULL AUTO_INCREMENT PRIMARY KEY);
CREATE TABLE test4(
a4 INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
b4 INT DEFAULT 0
);delimiter |CREATE TRIGGER testref BEFORE INSERT ON test1
FOR EACH ROW BEGIN
INSERT INTO test2 SET a2 = NEW.a1;
DELETE FROM test3 WHERE a3 = NEW.a1;
UPDATE test4 SET b4 = b4 + 1 WHERE a4 = NEW.a1;
END;
|delimiter ;INSERT INTO test3 (a3) VALUES
(NULL), (NULL), (NULL), (NULL), (NULL),
(NULL), (NULL), (NULL), (NULL), (NULL);INSERT INTO test4 (a4) VALUES
(0), (0), (0), (0), (0), (0), (0), (0), (0), (0);