sqlparser/ast/
dml.rs

1// Licensed to the Apache Software Foundation (ASF) under one
2// or more contributor license agreements.  See the NOTICE file
3// distributed with this work for additional information
4// regarding copyright ownership.  The ASF licenses this file
5// to you under the Apache License, Version 2.0 (the
6// "License"); you may not use this file except in compliance
7// with the License.  You may obtain a copy of the License at
8//
9//   http://www.apache.org/licenses/LICENSE-2.0
10//
11// Unless required by applicable law or agreed to in writing,
12// software distributed under the License is distributed on an
13// "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
14// KIND, either express or implied.  See the License for the
15// specific language governing permissions and limitations
16// under the License.
17
18#[cfg(not(feature = "std"))]
19use alloc::{boxed::Box, format, string::ToString, vec::Vec};
20
21use core::fmt::{self, Display};
22#[cfg(feature = "serde")]
23use serde::{Deserialize, Serialize};
24#[cfg(feature = "visitor")]
25use sqlparser_derive::{Visit, VisitMut};
26
27use crate::{
28    ast::display_separated,
29    display_utils::{indented_list, Indent, SpaceOrNewline},
30};
31
32use super::{
33    display_comma_separated, helpers::attached_token::AttachedToken, query::InputFormatClause,
34    Assignment, Expr, FromTable, Ident, InsertAliases, MysqlInsertPriority, ObjectName, OnInsert,
35    OptimizerHint, OrderByExpr, Query, SelectInto, SelectItem, Setting, SqliteOnConflict,
36    TableAliasWithoutColumns, TableFactor, TableObject, TableWithJoins, UpdateTableFromKind,
37    Values,
38};
39
40/// INSERT statement.
41#[derive(Debug, Clone, PartialEq, PartialOrd, Eq, Ord, Hash)]
42#[cfg_attr(feature = "serde", derive(Serialize, Deserialize))]
43#[cfg_attr(feature = "visitor", derive(Visit, VisitMut))]
44pub struct Insert {
45    /// Token for the `INSERT` keyword (or its substitutes)
46    pub insert_token: AttachedToken,
47    /// Query optimizer hints
48    ///
49    /// [MySQL](https://dev.mysql.com/doc/refman/8.4/en/optimizer-hints.html)
50    /// [Oracle](https://docs.oracle.com/en/database/oracle/oracle-database/21/sqlrf/Comments.html#GUID-D316D545-89E2-4D54-977F-FC97815CD62E)
51    pub optimizer_hints: Vec<OptimizerHint>,
52    /// Only for Sqlite
53    pub or: Option<SqliteOnConflict>,
54    /// Only for mysql
55    pub ignore: bool,
56    /// INTO - optional keyword
57    pub into: bool,
58    /// TABLE
59    pub table: TableObject,
60    /// `table_name as foo` (for PostgreSQL)
61    /// `table_name foo` (for Oracle)
62    pub table_alias: Option<TableAliasWithoutColumns>,
63    /// COLUMNS
64    pub columns: Vec<ObjectName>,
65    /// Overwrite (Hive)
66    pub overwrite: bool,
67    /// A SQL query that specifies what to insert
68    pub source: Option<Box<Query>>,
69    /// MySQL `INSERT INTO ... SET`
70    /// See: <https://dev.mysql.com/doc/refman/8.4/en/insert.html>
71    pub assignments: Vec<Assignment>,
72    /// partitioned insert (Hive)
73    pub partitioned: Option<Vec<Expr>>,
74    /// Columns defined after PARTITION
75    pub after_columns: Vec<Ident>,
76    /// whether the insert has the table keyword (Hive)
77    pub has_table_keyword: bool,
78    /// ON INSERT
79    pub on: Option<OnInsert>,
80    /// RETURNING
81    pub returning: Option<Vec<SelectItem>>,
82    /// OUTPUT (MSSQL)
83    /// See <https://learn.microsoft.com/en-us/sql/t-sql/queries/output-clause-transact-sql>
84    pub output: Option<OutputClause>,
85    /// Only for mysql
86    pub replace_into: bool,
87    /// Only for mysql
88    pub priority: Option<MysqlInsertPriority>,
89    /// Only for mysql
90    pub insert_alias: Option<InsertAliases>,
91    /// Settings used for ClickHouse.
92    ///
93    /// ClickHouse syntax: `INSERT INTO tbl SETTINGS format_template_resultset = '/some/path/resultset.format'`
94    ///
95    /// [ClickHouse `INSERT INTO`](https://clickhouse.com/docs/en/sql-reference/statements/insert-into)
96    pub settings: Option<Vec<Setting>>,
97    /// Format for `INSERT` statement when not using standard SQL format. Can be e.g. `CSV`,
98    /// `JSON`, `JSONAsString`, `LineAsString` and more.
99    ///
100    /// ClickHouse syntax: `INSERT INTO tbl FORMAT JSONEachRow {"foo": 1, "bar": 2}, {"foo": 3}`
101    ///
102    /// [ClickHouse formats JSON insert](https://clickhouse.com/docs/en/interfaces/formats#json-inserting-data)
103    pub format_clause: Option<InputFormatClause>,
104    /// For Snowflake multi-table insert: specifies the type (`ALL` or `FIRST`)
105    ///
106    /// - `None` means this is a regular single-table INSERT
107    /// - `Some(All)` means `INSERT ALL` (all matching WHEN clauses are executed)
108    /// - `Some(First)` means `INSERT FIRST` (only the first matching WHEN clause is executed)
109    ///
110    /// See: <https://docs.snowflake.com/en/sql-reference/sql/insert-multi-table>
111    pub multi_table_insert_type: Option<MultiTableInsertType>,
112    /// For multi-table insert: additional INTO clauses (unconditional)
113    ///
114    /// Used for `INSERT ALL INTO t1 INTO t2 ... SELECT ...`
115    ///
116    /// See: <https://docs.snowflake.com/en/sql-reference/sql/insert-multi-table>
117    pub multi_table_into_clauses: Vec<MultiTableInsertIntoClause>,
118    /// For conditional multi-table insert: WHEN clauses
119    ///
120    /// Used for `INSERT ALL/FIRST WHEN cond THEN INTO t1 ... SELECT ...`
121    ///
122    /// See: <https://docs.snowflake.com/en/sql-reference/sql/insert-multi-table>
123    pub multi_table_when_clauses: Vec<MultiTableInsertWhenClause>,
124    /// For conditional multi-table insert: ELSE clause
125    ///
126    /// See: <https://docs.snowflake.com/en/sql-reference/sql/insert-multi-table>
127    pub multi_table_else_clause: Option<Vec<MultiTableInsertIntoClause>>,
128}
129
130impl Display for Insert {
131    fn fmt(&self, f: &mut fmt::Formatter<'_>) -> fmt::Result {
132        // SQLite OR conflict has a special format: INSERT OR ... INTO table_name
133        let table_name = if let Some(table_alias) = &self.table_alias {
134            format!(
135                "{table} {as_keyword}{alias}",
136                table = self.table,
137                as_keyword = if table_alias.explicit { "AS " } else { "" },
138                alias = table_alias.alias
139            )
140        } else {
141            self.table.to_string()
142        };
143
144        if let Some(on_conflict) = self.or {
145            f.write_str("INSERT")?;
146            for hint in &self.optimizer_hints {
147                write!(f, " {hint}")?;
148            }
149            write!(f, " {on_conflict} INTO {table_name} ")?;
150        } else {
151            write!(
152                f,
153                "{start}",
154                start = if self.replace_into {
155                    "REPLACE"
156                } else {
157                    "INSERT"
158                }
159            )?;
160            for hint in &self.optimizer_hints {
161                write!(f, " {hint}")?;
162            }
163            if let Some(priority) = self.priority {
164                write!(f, " {priority}")?;
165            }
166
167            if self.ignore {
168                write!(f, " IGNORE")?;
169            }
170
171            if self.overwrite {
172                write!(f, " OVERWRITE")?;
173            }
174
175            if let Some(insert_type) = &self.multi_table_insert_type {
176                write!(f, " {}", insert_type)?;
177            }
178
179            if self.into {
180                write!(f, " INTO")?;
181            }
182
183            if self.has_table_keyword {
184                write!(f, " TABLE")?;
185            }
186
187            if !table_name.is_empty() {
188                write!(f, " {table_name} ")?;
189            }
190        }
191
192        if !self.columns.is_empty() {
193            write!(f, "({})", display_comma_separated(&self.columns))?;
194            SpaceOrNewline.fmt(f)?;
195        }
196
197        if let Some(ref parts) = self.partitioned {
198            if !parts.is_empty() {
199                write!(f, "PARTITION ({})", display_comma_separated(parts))?;
200                SpaceOrNewline.fmt(f)?;
201            }
202        }
203
204        if !self.after_columns.is_empty() {
205            write!(f, "({})", display_comma_separated(&self.after_columns))?;
206            SpaceOrNewline.fmt(f)?;
207        }
208
209        if let Some(output) = &self.output {
210            write!(f, "{output}")?;
211            SpaceOrNewline.fmt(f)?;
212        }
213
214        if let Some(settings) = &self.settings {
215            write!(f, "SETTINGS {}", display_comma_separated(settings))?;
216            SpaceOrNewline.fmt(f)?;
217        }
218
219        for into_clause in &self.multi_table_into_clauses {
220            SpaceOrNewline.fmt(f)?;
221            write!(f, "{}", into_clause)?;
222        }
223
224        for when_clause in &self.multi_table_when_clauses {
225            SpaceOrNewline.fmt(f)?;
226            write!(f, "{}", when_clause)?;
227        }
228
229        if let Some(else_clauses) = &self.multi_table_else_clause {
230            SpaceOrNewline.fmt(f)?;
231            write!(f, "ELSE")?;
232            for into_clause in else_clauses {
233                SpaceOrNewline.fmt(f)?;
234                write!(f, "{}", into_clause)?;
235            }
236        }
237
238        if let Some(source) = &self.source {
239            if !self.multi_table_into_clauses.is_empty()
240                || !self.multi_table_when_clauses.is_empty()
241            {
242                SpaceOrNewline.fmt(f)?;
243            }
244            source.fmt(f)?;
245        } else if !self.assignments.is_empty() {
246            write!(f, "SET")?;
247            indented_list(f, &self.assignments)?;
248        } else if let Some(format_clause) = &self.format_clause {
249            format_clause.fmt(f)?;
250        } else if self.columns.is_empty() {
251            write!(f, "DEFAULT VALUES")?;
252        }
253
254        if let Some(insert_alias) = &self.insert_alias {
255            write!(f, " AS {0}", insert_alias.row_alias)?;
256
257            if let Some(col_aliases) = &insert_alias.col_aliases {
258                if !col_aliases.is_empty() {
259                    write!(f, " ({})", display_comma_separated(col_aliases))?;
260                }
261            }
262        }
263
264        if let Some(on) = &self.on {
265            write!(f, "{on}")?;
266        }
267
268        if let Some(returning) = &self.returning {
269            SpaceOrNewline.fmt(f)?;
270            f.write_str("RETURNING")?;
271            indented_list(f, returning)?;
272        }
273
274        Ok(())
275    }
276}
277
278/// DELETE statement.
279#[derive(Debug, Clone, PartialEq, PartialOrd, Eq, Ord, Hash)]
280#[cfg_attr(feature = "serde", derive(Serialize, Deserialize))]
281#[cfg_attr(feature = "visitor", derive(Visit, VisitMut))]
282pub struct Delete {
283    /// Token for the `DELETE` keyword
284    pub delete_token: AttachedToken,
285    /// Query optimizer hints
286    ///
287    /// [MySQL](https://dev.mysql.com/doc/refman/8.4/en/optimizer-hints.html)
288    /// [Oracle](https://docs.oracle.com/en/database/oracle/oracle-database/21/sqlrf/Comments.html#GUID-D316D545-89E2-4D54-977F-FC97815CD62E)
289    pub optimizer_hints: Vec<OptimizerHint>,
290    /// Multi tables delete are supported in mysql
291    pub tables: Vec<ObjectName>,
292    /// FROM
293    pub from: FromTable,
294    /// USING (Snowflake, Postgres, MySQL)
295    pub using: Option<Vec<TableWithJoins>>,
296    /// WHERE
297    pub selection: Option<Expr>,
298    /// RETURNING
299    pub returning: Option<Vec<SelectItem>>,
300    /// OUTPUT (MSSQL)
301    /// See <https://learn.microsoft.com/en-us/sql/t-sql/queries/output-clause-transact-sql>
302    pub output: Option<OutputClause>,
303    /// ORDER BY (MySQL)
304    pub order_by: Vec<OrderByExpr>,
305    /// LIMIT (MySQL)
306    pub limit: Option<Expr>,
307}
308
309impl Display for Delete {
310    fn fmt(&self, f: &mut fmt::Formatter<'_>) -> fmt::Result {
311        f.write_str("DELETE")?;
312        for hint in &self.optimizer_hints {
313            f.write_str(" ")?;
314            hint.fmt(f)?;
315        }
316        if !self.tables.is_empty() {
317            indented_list(f, &self.tables)?;
318        }
319        match &self.from {
320            FromTable::WithFromKeyword(from) => {
321                f.write_str(" FROM")?;
322                indented_list(f, from)?;
323            }
324            FromTable::WithoutKeyword(from) => {
325                indented_list(f, from)?;
326            }
327        }
328        if let Some(output) = &self.output {
329            SpaceOrNewline.fmt(f)?;
330            write!(f, "{output}")?;
331        }
332        if let Some(using) = &self.using {
333            SpaceOrNewline.fmt(f)?;
334            f.write_str("USING")?;
335            indented_list(f, using)?;
336        }
337        if let Some(selection) = &self.selection {
338            SpaceOrNewline.fmt(f)?;
339            f.write_str("WHERE")?;
340            SpaceOrNewline.fmt(f)?;
341            Indent(selection).fmt(f)?;
342        }
343        if let Some(returning) = &self.returning {
344            SpaceOrNewline.fmt(f)?;
345            f.write_str("RETURNING")?;
346            indented_list(f, returning)?;
347        }
348        if !self.order_by.is_empty() {
349            SpaceOrNewline.fmt(f)?;
350            f.write_str("ORDER BY")?;
351            indented_list(f, &self.order_by)?;
352        }
353        if let Some(limit) = &self.limit {
354            SpaceOrNewline.fmt(f)?;
355            f.write_str("LIMIT")?;
356            SpaceOrNewline.fmt(f)?;
357            Indent(limit).fmt(f)?;
358        }
359        Ok(())
360    }
361}
362
363/// UPDATE statement.
364#[derive(Debug, Clone, PartialEq, PartialOrd, Eq, Ord, Hash)]
365#[cfg_attr(feature = "serde", derive(Serialize, Deserialize))]
366#[cfg_attr(feature = "visitor", derive(Visit, VisitMut))]
367pub struct Update {
368    /// Token for the `UPDATE` keyword
369    pub update_token: AttachedToken,
370    /// Query optimizer hints
371    ///
372    /// [MySQL](https://dev.mysql.com/doc/refman/8.4/en/optimizer-hints.html)
373    /// [Oracle](https://docs.oracle.com/en/database/oracle/oracle-database/21/sqlrf/Comments.html#GUID-D316D545-89E2-4D54-977F-FC97815CD62E)
374    pub optimizer_hints: Vec<OptimizerHint>,
375    /// TABLE
376    pub table: TableWithJoins,
377    /// Column assignments
378    pub assignments: Vec<Assignment>,
379    /// Table which provide value to be set
380    pub from: Option<UpdateTableFromKind>,
381    /// WHERE
382    pub selection: Option<Expr>,
383    /// RETURNING
384    pub returning: Option<Vec<SelectItem>>,
385    /// OUTPUT (MSSQL)
386    /// See <https://learn.microsoft.com/en-us/sql/t-sql/queries/output-clause-transact-sql>
387    pub output: Option<OutputClause>,
388    /// SQLite-specific conflict resolution clause
389    pub or: Option<SqliteOnConflict>,
390    /// ORDER BY (MySQL extension for single-table UPDATE)
391    /// See <https://dev.mysql.com/doc/refman/8.4/en/update.html>
392    pub order_by: Vec<OrderByExpr>,
393    /// LIMIT
394    pub limit: Option<Expr>,
395}
396
397impl Display for Update {
398    fn fmt(&self, f: &mut fmt::Formatter) -> fmt::Result {
399        f.write_str("UPDATE")?;
400        for hint in &self.optimizer_hints {
401            f.write_str(" ")?;
402            hint.fmt(f)?;
403        }
404        f.write_str(" ")?;
405        if let Some(or) = &self.or {
406            or.fmt(f)?;
407            f.write_str(" ")?;
408        }
409        self.table.fmt(f)?;
410        if let Some(UpdateTableFromKind::BeforeSet(from)) = &self.from {
411            SpaceOrNewline.fmt(f)?;
412            f.write_str("FROM")?;
413            indented_list(f, from)?;
414        }
415        if !self.assignments.is_empty() {
416            SpaceOrNewline.fmt(f)?;
417            f.write_str("SET")?;
418            indented_list(f, &self.assignments)?;
419        }
420        if let Some(output) = &self.output {
421            SpaceOrNewline.fmt(f)?;
422            write!(f, "{output}")?;
423        }
424        if let Some(UpdateTableFromKind::AfterSet(from)) = &self.from {
425            SpaceOrNewline.fmt(f)?;
426            f.write_str("FROM")?;
427            indented_list(f, from)?;
428        }
429        if let Some(selection) = &self.selection {
430            SpaceOrNewline.fmt(f)?;
431            f.write_str("WHERE")?;
432            SpaceOrNewline.fmt(f)?;
433            Indent(selection).fmt(f)?;
434        }
435        if let Some(returning) = &self.returning {
436            SpaceOrNewline.fmt(f)?;
437            f.write_str("RETURNING")?;
438            indented_list(f, returning)?;
439        }
440        if !self.order_by.is_empty() {
441            SpaceOrNewline.fmt(f)?;
442            f.write_str("ORDER BY")?;
443            indented_list(f, &self.order_by)?;
444        }
445        if let Some(limit) = &self.limit {
446            SpaceOrNewline.fmt(f)?;
447            write!(f, "LIMIT {limit}")?;
448        }
449        Ok(())
450    }
451}
452
453/// A `MERGE` statement.
454#[derive(Debug, Clone, PartialEq, PartialOrd, Eq, Ord, Hash)]
455#[cfg_attr(feature = "serde", derive(Serialize, Deserialize))]
456#[cfg_attr(feature = "visitor", derive(Visit, VisitMut))]
457pub struct Merge {
458    /// The `MERGE` token that starts the statement.
459    pub merge_token: AttachedToken,
460    /// Query optimizer hints
461    ///
462    /// [Oracle](https://docs.oracle.com/en/database/oracle/oracle-database/21/sqlrf/Comments.html#GUID-D316D545-89E2-4D54-977F-FC97815CD62E)
463    pub optimizer_hints: Vec<OptimizerHint>,
464    /// optional INTO keyword
465    pub into: bool,
466    /// Specifies the table to merge
467    pub table: TableFactor,
468    /// Specifies the table or subquery to join with the target table
469    pub source: TableFactor,
470    /// Specifies the expression on which to join the target table and source
471    pub on: Box<Expr>,
472    /// Specifies the actions to perform when values match or do not match.
473    pub clauses: Vec<MergeClause>,
474    /// Specifies the output to save changes in MSSQL
475    pub output: Option<OutputClause>,
476}
477
478impl Display for Merge {
479    fn fmt(&self, f: &mut fmt::Formatter<'_>) -> fmt::Result {
480        f.write_str("MERGE")?;
481        for hint in &self.optimizer_hints {
482            write!(f, " {hint}")?;
483        }
484        if self.into {
485            write!(f, " INTO")?;
486        }
487        write!(
488            f,
489            " {table} USING {source} ",
490            table = self.table,
491            source = self.source
492        )?;
493        write!(f, "ON {on} ", on = self.on)?;
494        write!(f, "{}", display_separated(&self.clauses, " "))?;
495        if let Some(ref output) = self.output {
496            write!(f, " {output}")?;
497        }
498        Ok(())
499    }
500}
501
502/// A `WHEN` clause within a `MERGE` Statement
503///
504/// Example:
505/// ```sql
506/// WHEN NOT MATCHED BY SOURCE AND product LIKE '%washer%' THEN DELETE
507/// ```
508/// [Snowflake](https://docs.snowflake.com/en/sql-reference/sql/merge)
509/// [BigQuery](https://cloud.google.com/bigquery/docs/reference/standard-sql/dml-syntax#merge_statement)
510#[derive(Debug, Clone, PartialEq, PartialOrd, Eq, Ord, Hash)]
511#[cfg_attr(feature = "serde", derive(Serialize, Deserialize))]
512#[cfg_attr(feature = "visitor", derive(Visit, VisitMut))]
513pub struct MergeClause {
514    /// The `WHEN` token that starts the sub-expression.
515    pub when_token: AttachedToken,
516    /// The type of `WHEN` clause.
517    pub clause_kind: MergeClauseKind,
518    /// An optional predicate to further restrict the clause.
519    pub predicate: Option<Expr>,
520    /// The action to perform when the clause is matched.
521    pub action: MergeAction,
522}
523
524impl Display for MergeClause {
525    fn fmt(&self, f: &mut fmt::Formatter) -> fmt::Result {
526        let MergeClause {
527            when_token: _,
528            clause_kind,
529            predicate,
530            action,
531        } = self;
532
533        write!(f, "WHEN {clause_kind}")?;
534        if let Some(pred) = predicate {
535            write!(f, " AND {pred}")?;
536        }
537        write!(f, " THEN {action}")
538    }
539}
540
541/// Variant of `WHEN` clause used within a `MERGE` Statement.
542///
543/// Example:
544/// ```sql
545/// MERGE INTO T USING U ON FALSE WHEN MATCHED THEN DELETE
546/// ```
547/// [Snowflake](https://docs.snowflake.com/en/sql-reference/sql/merge)
548/// [BigQuery](https://cloud.google.com/bigquery/docs/reference/standard-sql/dml-syntax#merge_statement)
549#[derive(Debug, Clone, Copy, PartialEq, PartialOrd, Eq, Ord, Hash)]
550#[cfg_attr(feature = "serde", derive(Serialize, Deserialize))]
551#[cfg_attr(feature = "visitor", derive(Visit, VisitMut))]
552pub enum MergeClauseKind {
553    /// `WHEN MATCHED`
554    Matched,
555    /// `WHEN NOT MATCHED`
556    NotMatched,
557    /// `WHEN MATCHED BY TARGET`
558    ///
559    /// [BigQuery](https://cloud.google.com/bigquery/docs/reference/standard-sql/dml-syntax#merge_statement)
560    NotMatchedByTarget,
561    /// `WHEN MATCHED BY SOURCE`
562    ///
563    /// [BigQuery](https://cloud.google.com/bigquery/docs/reference/standard-sql/dml-syntax#merge_statement)
564    NotMatchedBySource,
565}
566
567impl Display for MergeClauseKind {
568    fn fmt(&self, f: &mut fmt::Formatter) -> fmt::Result {
569        match self {
570            MergeClauseKind::Matched => write!(f, "MATCHED"),
571            MergeClauseKind::NotMatched => write!(f, "NOT MATCHED"),
572            MergeClauseKind::NotMatchedByTarget => write!(f, "NOT MATCHED BY TARGET"),
573            MergeClauseKind::NotMatchedBySource => write!(f, "NOT MATCHED BY SOURCE"),
574        }
575    }
576}
577
578/// Underlying statement of a `WHEN` clause within a `MERGE` Statement
579///
580/// Example
581/// ```sql
582/// INSERT (product, quantity) VALUES(product, quantity)
583/// ```
584///
585/// [Snowflake](https://docs.snowflake.com/en/sql-reference/sql/merge)
586/// [BigQuery](https://cloud.google.com/bigquery/docs/reference/standard-sql/dml-syntax#merge_statement)
587/// [Oracle](https://docs.oracle.com/en/database/oracle/oracle-database/21/sqlrf/MERGE.html)
588#[derive(Debug, Clone, PartialEq, PartialOrd, Eq, Ord, Hash)]
589#[cfg_attr(feature = "serde", derive(Serialize, Deserialize))]
590#[cfg_attr(feature = "visitor", derive(Visit, VisitMut))]
591pub enum MergeAction {
592    /// An `INSERT` clause
593    ///
594    /// Example:
595    /// ```sql
596    /// INSERT (product, quantity) VALUES(product, quantity)
597    /// ```
598    Insert(MergeInsertExpr),
599    /// An `UPDATE` clause
600    ///
601    /// Example:
602    /// ```sql
603    /// UPDATE SET quantity = T.quantity + S.quantity
604    /// ```
605    Update(MergeUpdateExpr),
606    /// A plain `DELETE` clause
607    Delete {
608        /// The `DELETE` token that starts the sub-expression.
609        delete_token: AttachedToken,
610    },
611    /// A `DO NOTHING` clause.
612    DoNothing {
613        /// The `DO` token that starts the sub-expression.
614        do_token: AttachedToken,
615        /// The `NOTHING` token that ends the sub-expression.
616        nothing_token: AttachedToken,
617    },
618}
619
620impl Display for MergeAction {
621    fn fmt(&self, f: &mut fmt::Formatter) -> fmt::Result {
622        match self {
623            MergeAction::Insert(insert) => {
624                write!(f, "INSERT {insert}")
625            }
626            MergeAction::Update(update) => {
627                write!(f, "UPDATE {update}")
628            }
629            MergeAction::Delete { .. } => {
630                write!(f, "DELETE")
631            }
632            MergeAction::DoNothing { .. } => {
633                write!(f, "DO NOTHING")
634            }
635        }
636    }
637}
638
639/// The type of expression used to insert rows within a `MERGE` statement.
640///
641/// [Snowflake](https://docs.snowflake.com/en/sql-reference/sql/merge)
642/// [BigQuery](https://cloud.google.com/bigquery/docs/reference/standard-sql/dml-syntax#merge_statement)
643#[derive(Debug, Clone, PartialEq, PartialOrd, Eq, Ord, Hash)]
644#[cfg_attr(feature = "serde", derive(Serialize, Deserialize))]
645#[cfg_attr(feature = "visitor", derive(Visit, VisitMut))]
646pub enum MergeInsertKind {
647    /// The insert expression is defined from an explicit `VALUES` clause
648    ///
649    /// Example:
650    /// ```sql
651    /// INSERT VALUES(product, quantity)
652    /// ```
653    Values(Values),
654    /// The insert expression is defined using only the `ROW` keyword.
655    ///
656    /// Example:
657    /// ```sql
658    /// INSERT ROW
659    /// ```
660    /// [BigQuery](https://cloud.google.com/bigquery/docs/reference/standard-sql/dml-syntax#merge_statement)
661    Row,
662    /// The insert expression uses the `*` wildcard to insert all columns.
663    ///
664    /// Example:
665    /// ```sql
666    /// INSERT *
667    /// ```
668    /// [Databricks](https://docs.databricks.com/en/sql/language-manual/delta-merge-into.html)
669    Wildcard,
670}
671
672impl Display for MergeInsertKind {
673    fn fmt(&self, f: &mut fmt::Formatter) -> fmt::Result {
674        match self {
675            MergeInsertKind::Values(values) => {
676                write!(f, "{values}")
677            }
678            MergeInsertKind::Row => {
679                write!(f, "ROW")
680            }
681            MergeInsertKind::Wildcard => {
682                write!(f, "*")
683            }
684        }
685    }
686}
687
688/// The expression used to insert rows within a `MERGE` statement.
689///
690/// Examples
691/// ```sql
692/// INSERT (product, quantity) VALUES(product, quantity)
693/// INSERT ROW
694/// ```
695///
696/// [Snowflake](https://docs.snowflake.com/en/sql-reference/sql/merge)
697/// [BigQuery](https://cloud.google.com/bigquery/docs/reference/standard-sql/dml-syntax#merge_statement)
698/// [Oracle](https://docs.oracle.com/en/database/oracle/oracle-database/21/sqlrf/MERGE.html)
699#[derive(Debug, Clone, PartialEq, PartialOrd, Eq, Ord, Hash)]
700#[cfg_attr(feature = "serde", derive(Serialize, Deserialize))]
701#[cfg_attr(feature = "visitor", derive(Visit, VisitMut))]
702pub struct MergeInsertExpr {
703    /// The `INSERT` token that starts the sub-expression.
704    pub insert_token: AttachedToken,
705    /// Columns (if any) specified by the insert.
706    ///
707    /// Example:
708    /// ```sql
709    /// INSERT (product, quantity) VALUES(product, quantity)
710    /// INSERT (product, quantity) ROW
711    /// ```
712    pub columns: Vec<ObjectName>,
713    /// The token, `[VALUES | ROW]` starting `kind`.
714    pub kind_token: AttachedToken,
715    /// The insert type used by the statement.
716    pub kind: MergeInsertKind,
717    /// An optional condition to restrict the insertion (Oracle specific)
718    pub insert_predicate: Option<Expr>,
719}
720
721impl Display for MergeInsertExpr {
722    fn fmt(&self, f: &mut fmt::Formatter) -> fmt::Result {
723        if !self.columns.is_empty() {
724            write!(f, "({}) ", display_comma_separated(self.columns.as_slice()))?;
725        }
726        write!(f, "{}", self.kind)?;
727        if let Some(predicate) = self.insert_predicate.as_ref() {
728            write!(f, " WHERE {}", predicate)?;
729        }
730        Ok(())
731    }
732}
733
734/// The kind of update used within a `MERGE` statement.
735#[derive(Debug, Clone, PartialEq, PartialOrd, Eq, Ord, Hash)]
736#[cfg_attr(feature = "serde", derive(Serialize, Deserialize))]
737#[cfg_attr(feature = "visitor", derive(Visit, VisitMut))]
738pub enum MergeUpdateKind {
739    /// Standard update with explicit assignments.
740    ///
741    /// Example:
742    /// ```sql
743    /// UPDATE SET quantity = source.quantity, name = source.name
744    /// ```
745    Set(Vec<Assignment>),
746    /// The `*` wildcard to update all columns from the source.
747    ///
748    /// Example:
749    /// ```sql
750    /// UPDATE SET *
751    /// ```
752    /// [Databricks](https://docs.databricks.com/en/sql/language-manual/delta-merge-into.html)
753    Wildcard,
754}
755
756impl Display for MergeUpdateKind {
757    fn fmt(&self, f: &mut fmt::Formatter<'_>) -> fmt::Result {
758        match self {
759            MergeUpdateKind::Set(assignments) => {
760                write!(f, "SET {}", display_comma_separated(assignments))
761            }
762            MergeUpdateKind::Wildcard => {
763                write!(f, "SET *")
764            }
765        }
766    }
767}
768
769/// The expression used to update rows within a `MERGE` statement.
770///
771/// Examples
772/// ```sql
773/// UPDATE SET quantity = T.quantity + S.quantity
774/// UPDATE SET *
775/// ```
776///
777/// [Snowflake](https://docs.snowflake.com/en/sql-reference/sql/merge)
778/// [BigQuery](https://cloud.google.com/bigquery/docs/reference/standard-sql/dml-syntax#merge_statement)
779/// [Oracle](https://docs.oracle.com/en/database/oracle/oracle-database/21/sqlrf/MERGE.html)
780/// [Databricks](https://docs.databricks.com/en/sql/language-manual/delta-merge-into.html)
781#[derive(Debug, Clone, PartialEq, PartialOrd, Eq, Ord, Hash)]
782#[cfg_attr(feature = "serde", derive(Serialize, Deserialize))]
783#[cfg_attr(feature = "visitor", derive(Visit, VisitMut))]
784pub struct MergeUpdateExpr {
785    /// The `UPDATE` token that starts the sub-expression.
786    pub update_token: AttachedToken,
787    /// The kind of update: explicit assignments or `*` shorthand.
788    pub kind: MergeUpdateKind,
789    /// `where_clause` for the update (Oracle specific)
790    pub update_predicate: Option<Expr>,
791    /// `delete_clause` for the update "delete where" (Oracle specific)
792    pub delete_predicate: Option<Expr>,
793}
794
795impl Display for MergeUpdateExpr {
796    fn fmt(&self, f: &mut fmt::Formatter<'_>) -> fmt::Result {
797        write!(f, "{}", self.kind)?;
798        if let Some(predicate) = self.update_predicate.as_ref() {
799            write!(f, " WHERE {predicate}")?;
800        }
801        if let Some(predicate) = self.delete_predicate.as_ref() {
802            write!(f, " DELETE WHERE {predicate}")?;
803        }
804        Ok(())
805    }
806}
807
808/// An `OUTPUT` clause on `MERGE`, `INSERT`, `UPDATE`, or `DELETE` (MSSQL).
809///
810/// Example:
811/// OUTPUT $action, deleted.* INTO dbo.temp_products;
812/// <https://learn.microsoft.com/en-us/sql/t-sql/queries/output-clause-transact-sql>
813#[derive(Debug, Clone, PartialEq, PartialOrd, Eq, Ord, Hash)]
814#[cfg_attr(feature = "serde", derive(Serialize, Deserialize))]
815#[cfg_attr(feature = "visitor", derive(Visit, VisitMut))]
816pub enum OutputClause {
817    /// `OUTPUT` clause
818    Output {
819        /// The `OUTPUT` token that starts the sub-expression.
820        output_token: AttachedToken,
821        /// The select items to output
822        select_items: Vec<SelectItem>,
823        /// Optional `INTO` table to direct the output
824        into_table: Option<SelectInto>,
825    },
826    /// `RETURNING` clause
827    Returning {
828        /// The `RETURNING` token that starts the sub-expression.
829        returning_token: AttachedToken,
830        /// The select items to return
831        select_items: Vec<SelectItem>,
832    },
833}
834
835impl fmt::Display for OutputClause {
836    fn fmt(&self, f: &mut fmt::Formatter) -> fmt::Result {
837        match self {
838            OutputClause::Output {
839                output_token: _,
840                select_items,
841                into_table,
842            } => {
843                f.write_str("OUTPUT ")?;
844                display_comma_separated(select_items).fmt(f)?;
845                if let Some(into_table) = into_table {
846                    f.write_str(" ")?;
847                    into_table.fmt(f)?;
848                }
849                Ok(())
850            }
851            OutputClause::Returning {
852                returning_token: _,
853                select_items,
854            } => {
855                f.write_str("RETURNING ")?;
856                display_comma_separated(select_items).fmt(f)
857            }
858        }
859    }
860}
861
862/// A WHEN clause in a conditional multi-table INSERT.
863///
864/// Syntax:
865/// ```sql
866/// WHEN n1 > 100 THEN
867///   INTO t1
868///   INTO t2 (c1, c2) VALUES (n1, n2)
869/// ```
870#[derive(Debug, Clone, PartialEq, PartialOrd, Eq, Ord, Hash)]
871#[cfg_attr(feature = "serde", derive(Serialize, Deserialize))]
872#[cfg_attr(feature = "visitor", derive(Visit, VisitMut))]
873pub struct MultiTableInsertWhenClause {
874    /// The condition for this WHEN clause
875    pub condition: Expr,
876    /// The INTO clauses to execute when the condition is true
877    pub into_clauses: Vec<MultiTableInsertIntoClause>,
878}
879
880impl Display for MultiTableInsertWhenClause {
881    fn fmt(&self, f: &mut fmt::Formatter<'_>) -> fmt::Result {
882        write!(f, "WHEN {} THEN", self.condition)?;
883        for into_clause in &self.into_clauses {
884            SpaceOrNewline.fmt(f)?;
885            write!(f, "{}", into_clause)?;
886        }
887        Ok(())
888    }
889}
890
891/// An INTO clause in a multi-table INSERT.
892///
893/// Syntax:
894/// ```sql
895/// INTO <target_table> [ ( <target_col_name> [ , ... ] ) ] [ VALUES ( { <source_col_name> | DEFAULT | NULL } [ , ... ] ) ]
896/// ```
897#[derive(Debug, Clone, PartialEq, PartialOrd, Eq, Ord, Hash)]
898#[cfg_attr(feature = "serde", derive(Serialize, Deserialize))]
899#[cfg_attr(feature = "visitor", derive(Visit, VisitMut))]
900pub struct MultiTableInsertIntoClause {
901    /// The target table
902    pub table_name: ObjectName,
903    /// The target columns (optional)
904    pub columns: Vec<Ident>,
905    /// The VALUES clause (optional)
906    pub values: Option<MultiTableInsertValues>,
907}
908
909impl Display for MultiTableInsertIntoClause {
910    fn fmt(&self, f: &mut fmt::Formatter<'_>) -> fmt::Result {
911        write!(f, "INTO {}", self.table_name)?;
912        if !self.columns.is_empty() {
913            write!(f, " ({})", display_comma_separated(&self.columns))?;
914        }
915        if let Some(values) = &self.values {
916            write!(f, " VALUES ({})", display_comma_separated(&values.values))?;
917        }
918        Ok(())
919    }
920}
921
922/// The VALUES clause in a multi-table INSERT INTO clause.
923#[derive(Debug, Clone, PartialEq, PartialOrd, Eq, Ord, Hash)]
924#[cfg_attr(feature = "serde", derive(Serialize, Deserialize))]
925#[cfg_attr(feature = "visitor", derive(Visit, VisitMut))]
926pub struct MultiTableInsertValues {
927    /// The values to insert (can be column references, DEFAULT, or NULL)
928    pub values: Vec<MultiTableInsertValue>,
929}
930
931/// A value in a multi-table INSERT VALUES clause.
932#[derive(Debug, Clone, PartialEq, PartialOrd, Eq, Ord, Hash)]
933#[cfg_attr(feature = "serde", derive(Serialize, Deserialize))]
934#[cfg_attr(feature = "visitor", derive(Visit, VisitMut))]
935pub enum MultiTableInsertValue {
936    /// A column reference or expression from the source
937    Expr(Expr),
938    /// The DEFAULT keyword
939    Default,
940}
941
942impl Display for MultiTableInsertValue {
943    fn fmt(&self, f: &mut fmt::Formatter<'_>) -> fmt::Result {
944        match self {
945            MultiTableInsertValue::Expr(expr) => write!(f, "{}", expr),
946            MultiTableInsertValue::Default => write!(f, "DEFAULT"),
947        }
948    }
949}
950
951/// The type of multi-table INSERT statement(Snowflake).
952///
953/// See: <https://docs.snowflake.com/en/sql-reference/sql/insert-multi-table>
954#[derive(Debug, Clone, PartialEq, PartialOrd, Eq, Ord, Hash)]
955#[cfg_attr(feature = "serde", derive(Serialize, Deserialize))]
956#[cfg_attr(feature = "visitor", derive(Visit, VisitMut))]
957pub enum MultiTableInsertType {
958    /// `INSERT ALL` - all matching WHEN clauses are executed
959    All,
960    /// `INSERT FIRST` - only the first matching WHEN clause is executed
961    First,
962}
963
964impl Display for MultiTableInsertType {
965    fn fmt(&self, f: &mut fmt::Formatter<'_>) -> fmt::Result {
966        match self {
967            MultiTableInsertType::All => write!(f, "ALL"),
968            MultiTableInsertType::First => write!(f, "FIRST"),
969        }
970    }
971}
sqlparser/ast/dml.rs

sqlparser/ast/
dml.rs
