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

sqlparser/ast/
dml.rs
