有两张表,第一张表session如下:
mysql> desc session;
+-----------------------+--------------+------+-----+---------------------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-----------------------+--------------+------+-----+---------------------+----------------+
| sequence | int(11) | | PRI | NULL | auto_increment |
| application | varchar(255) | YES | | NULL | |
| sessionType | varchar(255) | YES | | NULL | |
| descr | varchar(255) | YES | | NULL | |
| processlayer_sequence | int(11) | YES | | -1 | |
| product_sequence | int(11) | YES | | -1 | |
| machineSn | text | YES | | NULL | |
| action | int(11) | YES | | NULL | |
| status | int(11) | YES | | 0 | |
| owner | varchar(64) | YES | | NULL | |
| message | varchar(255) | YES | | NULL | |
| alert | int(11) | YES | | 0 | |
| app_version | varchar(255) | YES | | NULL | |
| updateTime | timestamp | YES | | CURRENT_TIMESTAMP | |
| startTime | timestamp | YES | | 0000-00-00 00:00:00 | |
| completeTime | timestamp | YES | | 0000-00-00 00:00:00 | |
+-----------------------+--------------+------+-----+---------------------+----------------+
第二张表processed_data_mgr如下:
mysql> desc processed_data_mgr;
+-----------------------+--------------+------+-----+-------------------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-----------------------+--------------+------+-----+-------------------+----------------+
| sequence | int(11) | | PRI | NULL | auto_increment |
| session_sequence | int(11) | YES | | -1 | |
| processlayer_sequence | int(11) | YES | | -1 | |
| product_sequence | int(11) | YES | | -1 | |
| machineSn | text | YES | | NULL | |
| descr | varchar(255) | YES | | NULL | |
| update_time | timestamp | YES | | CURRENT_TIMESTAMP | |
+-----------------------+--------------+------+-----+-------------------+----------------+
其中,processed_data_mgr表中的session_sequence,processlayer_sequence,product_sequence,machineSn分别对应于session表中的sequence,processlayer_sequence,product_sequence,machineSn。
现在想要给processed_data_mgr表增加一条记录,其中与session表的sequence=135相关。
很容易想到的办法,就是先后使用SELECT语句和INSERT语句:先用SELECT语句从session表中拿到数据,再用INSERT语句填入processed_data_mgr表中。
用两条SQL语句很麻烦,能不能只用一条语句呢?
可以的,如下所示:
mysql> INSERT INTO processed_data_mgr (session_sequence, processlayer_sequence, product_sequence, machineSn, descr)
-> SELECT session.sequence, session.processlayer_sequence, session.product_sequence, session.machineSn, 'descr1'
-> FROM session WHERE session.sequence = 135;
Query OK, 1 row affected (0.00 sec)
Records: 1 Duplicates: 0 Warnings: 0
mysql> select * from processed_data_mgr\G
*************************** 1. row ***************************
sequence: 1
session_sequence: 135
processlayer_sequence: 2
product_sequence: 1
machineSn: 1001
descr: descr1
update_time: 2013-04-05 16:05:31
1 row in set (0.03 sec)
---------------------- 本博客所有内容均为原创,转载请注明作者和出处 -----------------------
作者:刘文哲
联系方式:liuwenzhe2008@qq.com
博客:http://blog.csdn.net/liuwenzhe2008