如何在INSERT语句中使用SELECT的结果

来源：互联网发布：市场营销知乎编辑：程序博客网时间：2024/05/29 10:02

有两张表，第一张表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: 0mysql> select * from processed_data_mgr\G*************************** 1. row ***************************             sequence: 1     session_sequence: 135processlayer_sequence: 2     product_sequence: 1            machineSn: 1001                descr: descr1          update_time: 2013-04-05 16:05:311 row in set (0.03 sec)

---------------------- 本博客所有内容均为原创，转载请注明作者和出处 -----------------------

作者：刘文哲

联系方式：liuwenzhe2008@qq.com

博客：http://blog.csdn.net/liuwenzhe2008

-------------------------------------------------------------------------------------------------------------