INSERT ... SELECT Syntax

MySql : 3.23 이상사용가능,

- 단순히 다른테이블의 컬럼만 가져와서 업데이트 가능

- 특정값은 지정된값을 사용하고, 다른테이블의 특정컬럼값만 가져와서 업데이트 가능

  ==>   INSERT INTO `_test` 
          (time,val1,val2,total)
           SELECT  NOW(),$_POST['val1'],$_POST['val2'], sum(total)
            FROM `_test`  WHERE val1 < 100

 

=====================================================================================
http://dev.mysql.com/doc/refman/4.1/en/insert-select.html

 INSERT [LOW_PRIORITY | HIGH_PRIORITY] [IGNORE] 
    [INTO] tbl_name [(col_name,...)]
  SELECT ...
[ ON DUPLICATE KEY UPDATE col_name=expr, ... ]

With INSERT ... SELECT, you can quickly insert many rows into a table from one or many tables. For example:

INSERT INTO tbl_temp2 (fld_id)
  SELECT tbl_temp1.fld_order_id
  FROM tbl_temp1 WHERE tbl_temp1.fld_order_id > 100;

The following conditions hold for a INSERT ... SELECT statements:

• Prior to MySQL 4.0.1, INSERT ... SELECT implicitly operates in IGNORE mode. As of MySQL 4.0.1, specify IGNORE explicitly to ignore rows that would cause duplicate-key violations.

• DELAYED is ignored with INSERT ... SELECT.

• Prior to MySQL 4.0.14, the target table of the INSERT statement cannot appear in the FROM clause of the SELECT part of the query. This limitation is lifted in 4.0.14. In this case, MySQL creates a temporary table to hold the rows from the SELECT and then inserts those rows into the target table. However, it remains true that you cannot use INSERT INTO t ... SELECT ... FROM t when t is a TEMPORARY table, because TEMPORARY tables cannot be referred to twice in the same statement (see Section A.1.7.3, “TEMPORARY TABLE Problems”).

• AUTO_INCREMENT columns work as usual.

• To ensure that the binary log can be used to re-create the original tables, MySQL does not allow concurrent inserts for INSERT ... SELECT statements.

• Currently, you cannot insert into a table and select from the same table in a subquery.

• To avoid ambigious column reference problems when the SELECT and the INSERT refer to the same table, provide a unique alias for each table used in the SELECT part, and qualify column names in that part with the appropriate alias.

In the values part of ON DUPLICATE KEY UPDATE, you can refer to columns in other tables, as long as you do not use GROUP BY in the SELECT part. One side effect is that you must qualify non-unique column names in the values part.