0001 ---
0002 layout: global
0003 title: INSERT OVERWRITE
0004 displayTitle: INSERT OVERWRITE
0005 license: |
0006 Licensed to the Apache Software Foundation (ASF) under one or more
0007 contributor license agreements. See the NOTICE file distributed with
0008 this work for additional information regarding copyright ownership.
0009 The ASF licenses this file to You under the Apache License, Version 2.0
0010 (the "License"); you may not use this file except in compliance with
0011 the License. You may obtain a copy of the License at
0012
0013 http://www.apache.org/licenses/LICENSE-2.0
0014
0015 Unless required by applicable law or agreed to in writing, software
0016 distributed under the License is distributed on an "AS IS" BASIS,
0017 WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
0018 See the License for the specific language governing permissions and
0019 limitations under the License.
0020 ---
0021
0022 ### Description
0023
0024 The `INSERT OVERWRITE` statement overwrites the existing data in the table using the new values. The inserted rows can be specified by value expressions or result from a query.
0025
0026 ### Syntax
0027
0028 ```sql
0029 INSERT OVERWRITE [ TABLE ] table_identifier [ partition_spec [ IF NOT EXISTS ] ]
0030 { VALUES ( { value | NULL } [ , ... ] ) [ , ( ... ) ] | query }
0031 ```
0032
0033 ### Parameters
0034
0035 * **table_identifier**
0036
0037 Specifies a table name, which may be optionally qualified with a database name.
0038
0039 **Syntax:** `[ database_name. ] table_name`
0040
0041 * **partition_spec**
0042
0043 An optional parameter that specifies a comma separated list of key and value pairs
0044 for partitions.
0045
0046 **Syntax:** `PARTITION ( partition_col_name [ = partition_col_val ] [ , ... ] )`
0047
0048 * **VALUES ( { value `|` NULL } [ , ... ] ) [ , ( ... ) ]**
0049
0050 Specifies the values to be inserted. Either an explicitly specified value or a NULL can be inserted.
0051 A comma must be used to separate each value in the clause. More than one set of values can be specified to insert multiple rows.
0052
0053 * **query**
0054
0055 A query that produces the rows to be inserted. It can be in one of following formats:
0056 * a `SELECT` statement
0057 * a `TABLE` statement
0058 * a `FROM` statement
0059
0060 ### Examples
0061
0062 #### Insert Using a VALUES Clause
0063
0064 ```sql
0065 -- Assuming the students table has already been created and populated.
0066 SELECT * FROM students;
0067 +-------------+-------------------------+----------+
0068 | name| address|student_id|
0069 +-------------+-------------------------+----------+
0070 | Amy Smith| 123 Park Ave, San Jose| 111111|
0071 | Bob Brown| 456 Taylor St, Cupertino| 222222|
0072 |Cathy Johnson| 789 Race Ave, Palo Alto| 333333|
0073 |Dora Williams|134 Forest Ave, Melo Park| 444444|
0074 |Fleur Laurent| 345 Copper St, London| 777777|
0075 |Gordon Martin| 779 Lake Ave, Oxford| 888888|
0076 | Helen Davis|469 Mission St, San Diego| 999999|
0077 | Jason Wang| 908 Bird St, Saratoga| 121212|
0078 +-------------+-------------------------+----------+
0079
0080 INSERT OVERWRITE students VALUES
0081 ('Ashua Hill', '456 Erica Ct, Cupertino', 111111),
0082 ('Brian Reed', '723 Kern Ave, Palo Alto', 222222);
0083
0084 SELECT * FROM students;
0085 +----------+-----------------------+----------+
0086 | name| address|student_id|
0087 +----------+-----------------------+----------+
0088 |Ashua Hill|456 Erica Ct, Cupertino| 111111|
0089 |Brian Reed|723 Kern Ave, Palo Alto| 222222|
0090 +----------+-----------------------+----------+
0091 ```
0092
0093 #### Insert Using a SELECT Statement
0094
0095 ```sql
0096 -- Assuming the persons table has already been created and populated.
0097 SELECT * FROM persons;
0098 +-------------+-------------------------+---------+
0099 | name| address| ssn|
0100 +-------------+-------------------------+---------+
0101 |Dora Williams|134 Forest Ave, Melo Park|123456789|
0102 +-------------+-------------------------+---------+
0103 | Eddie Davis| 245 Market St,Milpitas|345678901|
0104 +-------------+-------------------------+---------+
0105
0106 INSERT OVERWRITE students PARTITION (student_id = 222222)
0107 SELECT name, address FROM persons WHERE name = "Dora Williams";
0108
0109 SELECT * FROM students;
0110 +-------------+-------------------------+----------+
0111 | name| address|student_id|
0112 +-------------+-------------------------+----------+
0113 | Ashua Hill| 456 Erica Ct, Cupertino| 111111|
0114 +-------------+-------------------------+----------+
0115 |Dora Williams|134 Forest Ave, Melo Park| 222222|
0116 +-------------+-------------------------+----------+
0117 ```
0118
0119 #### Insert Using a TABLE Statement
0120
0121 ```sql
0122 -- Assuming the visiting_students table has already been created and populated.
0123 SELECT * FROM visiting_students;
0124 +-------------+---------------------+----------+
0125 | name| address|student_id|
0126 +-------------+---------------------+----------+
0127 |Fleur Laurent|345 Copper St, London| 777777|
0128 +-------------+---------------------+----------+
0129 |Gordon Martin| 779 Lake Ave, Oxford| 888888|
0130 +-------------+---------------------+----------+
0131
0132 INSERT OVERWRITE students TABLE visiting_students;
0133
0134 SELECT * FROM students;
0135 +-------------+---------------------+----------+
0136 | name| address|student_id|
0137 +-------------+---------------------+----------+
0138 |Fleur Laurent|345 Copper St, London| 777777|
0139 +-------------+---------------------+----------+
0140 |Gordon Martin| 779 Lake Ave, Oxford| 888888|
0141 +-------------+---------------------+----------+
0142 ```
0143
0144 #### Insert Using a FROM Statement
0145
0146 ```sql
0147 -- Assuming the applicants table has already been created and populated.
0148 SELECT * FROM applicants;
0149 +-----------+--------------------------+----------+---------+
0150 | name| address|student_id|qualified|
0151 +-----------+--------------------------+----------+---------+
0152 |Helen Davis| 469 Mission St, San Diego| 999999| true|
0153 +-----------+--------------------------+----------+---------+
0154 | Ivy King|367 Leigh Ave, Santa Clara| 101010| false|
0155 +-----------+--------------------------+----------+---------+
0156 | Jason Wang| 908 Bird St, Saratoga| 121212| true|
0157 +-----------+--------------------------+----------+---------+
0158
0159 INSERT OVERWRITE students
0160 FROM applicants SELECT name, address, id applicants WHERE qualified = true;
0161
0162 SELECT * FROM students;
0163 +-----------+-------------------------+----------+
0164 | name| address|student_id|
0165 +-----------+-------------------------+----------+
0166 |Helen Davis|469 Mission St, San Diego| 999999|
0167 +-----------+-------------------------+----------+
0168 | Jason Wang| 908 Bird St, Saratoga| 121212|
0169 +-----------+-------------------------+----------+
0170 ```
0171
0172 ### Related Statements
0173
0174 * [INSERT INTO statement](sql-ref-syntax-dml-insert-into.html)
0175 * [INSERT OVERWRITE DIRECTORY statement](sql-ref-syntax-dml-insert-overwrite-directory.html)
0176 * [INSERT OVERWRITE DIRECTORY with Hive format statement](sql-ref-syntax-dml-insert-overwrite-directory-hive.html)
