- 전체
- Sample DB
- database modeling
- [표준 SQL] Standard SQL
- G-SQL
- 10-Min
- ORACLE
- MS SQLserver
- MySQL
- SQLite
- postgreSQL
- 데이터아키텍처전문가 - 국가공인자격
- 데이터 분석 전문가 [ADP]
- [국가공인] SQL 개발자/전문가
- NoSQL
- hadoop
- hadoop eco system
- big data (빅데이터)
- stat(통계) R 언어
- XML DB & XQuery
- spark
- DataBase Tool
- 데이터분석 & 데이터사이언스
- Engineer Quality Management
- [기계학습] machine learning
- 데이터 수집 및 전처리
- 국가기술자격 빅데이터분석기사
- 암호화폐 (비트코인, cryptocurrency, bitcoin)
MySQL mysql select 한 값을 insert 하는 sql
2018.07.02 15:49
mysql select 한 값을 insert 하는 sql
13.2.6.1 INSERT ... SELECT Syntax
INSERT [LOW_PRIORITY | HIGH_PRIORITY] [IGNORE] [INTO] tbl_name [PARTITION (partition_name [, partition_name] ...)] [(col_name [, col_name] ...)] SELECT ... [ON DUPLICATE KEY UPDATE assignment_list] value: {expr | DEFAULT} assignment: col_name = value assignment_list: assignment [, assignment] ...With INSERT ... SELECT, you can quickly insert many rows into a table from the result of a SELECT statement, which can select 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 INSERT ... SELECT statements:
-
Specify
IGNOREto ignore rows that would cause duplicate-key violations. -
The target table of the
INSERTstatement may appear in theFROMclause of theSELECTpart of the query. However, you cannot insert into a table and select from the same table in a subquery.When selecting from and inserting into the same table, MySQL creates an internal temporary table to hold the rows from the
SELECTand then inserts those rows into the target table. However, you cannot useINSERT INTO t ... SELECT ... FROM twhentis aTEMPORARYtable, becauseTEMPORARYtables cannot be referred to twice in the same statement. See Section 8.4.4, “Internal Temporary Table Use in MySQL”, and Section B.5.6.2, “TEMPORARY Table Problems”. -
AUTO_INCREMENTcolumns work as usual. -
To ensure that the binary log can be used to re-create the original tables, MySQL does not permit concurrent inserts for
INSERT ... SELECTstatements (see Section 8.11.3, “Concurrent Inserts”). -
To avoid ambiguous column reference problems when the
SELECTand theINSERTrefer to the same table, provide a unique alias for each table used in theSELECTpart, and qualify column names in that part with the appropriate alias.
You can explicitly select which partitions or subpartitions (or both) of the source or target table (or both) are to be used with a PARTITION option following the name of the table. When PARTITION is used with the name of the source table in the SELECT portion of the statement, rows are selected only from the partitions or subpartitions named in its partition list. When PARTITION is used with the name of the target table for the INSERT portion of the statement, it must be possible to insert all rows selected into the partitions or subpartitions named in the partition list following the option. Otherwise, the INSERT ... SELECT statement fails. For more information and examples, see Section 22.5, “Partition Selection”.
For INSERT ... SELECT statements, see Section 13.2.6.2, “INSERT ... ON DUPLICATE KEY UPDATE Syntax” for conditions under which the SELECT columns can be referred to in an ON DUPLICATE KEY UPDATE clause.
The order in which a SELECT statement with no ORDER BY clause returns rows is nondeterministic. This means that, when using replication, there is no guarantee that such a SELECT returns rows in the same order on the master and the slave, which can lead to inconsistencies between them. To prevent this from occurring, always write INSERT ... SELECT statements that are to be replicated using an ORDER BY clause that produces the same row order on the master and the slave. See also Section 17.4.1.18, “Replication and LIMIT”.
Due to this issue, INSERT ... SELECT ON DUPLICATE KEY UPDATE and INSERT IGNORE ... SELECT statements are flagged as unsafe for statement-based replication. Such statements produce a warning in the error log when using statement-based mode and are written to the binary log using the row-based format when using MIXED mode. (Bug #11758262, Bug #50439)
See also Section 17.2.1.1, “Advantages and Disadvantages of Statement-Based and Row-Based Replication”.
An INSERT ... SELECT statement affecting partitioned tables using a storage engine such as MyISAM that employs table-level locks locks all partitions of the target table; however, only those partitions that are actually read from the source table are locked. (This does not occur with tables using storage engines such as InnoDB that employ row-level locking.) For more information, see Partitioning and Locking.
[출처] https://dev.mysql.com/doc/refman/8.0/en/insert-select.html
광고 클릭에서 발생하는 수익금은 모두 웹사이트 서버의 유지 및 관리, 그리고 기술 콘텐츠 향상을 위해 쓰여집니다.
