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}
612
613impl Display for MergeAction {
614    fn fmt(&self, f: &mut fmt::Formatter) -> fmt::Result {
615        match self {
616            MergeAction::Insert(insert) => {
617                write!(f, "INSERT {insert}")
618            }
619            MergeAction::Update(update) => {
620                write!(f, "UPDATE {update}")
621            }
622            MergeAction::Delete { .. } => {
623                write!(f, "DELETE")
624            }
625        }
626    }
627}
628
629/// The type of expression used to insert rows within a `MERGE` statement.
630///
631/// [Snowflake](https://docs.snowflake.com/en/sql-reference/sql/merge)
632/// [BigQuery](https://cloud.google.com/bigquery/docs/reference/standard-sql/dml-syntax#merge_statement)
633#[derive(Debug, Clone, PartialEq, PartialOrd, Eq, Ord, Hash)]
634#[cfg_attr(feature = "serde", derive(Serialize, Deserialize))]
635#[cfg_attr(feature = "visitor", derive(Visit, VisitMut))]
636pub enum MergeInsertKind {
637    /// The insert expression is defined from an explicit `VALUES` clause
638    ///
639    /// Example:
640    /// ```sql
641    /// INSERT VALUES(product, quantity)
642    /// ```
643    Values(Values),
644    /// The insert expression is defined using only the `ROW` keyword.
645    ///
646    /// Example:
647    /// ```sql
648    /// INSERT ROW
649    /// ```
650    /// [BigQuery](https://cloud.google.com/bigquery/docs/reference/standard-sql/dml-syntax#merge_statement)
651    Row,
652    /// The insert expression uses the `*` wildcard to insert all columns.
653    ///
654    /// Example:
655    /// ```sql
656    /// INSERT *
657    /// ```
658    /// [Databricks](https://docs.databricks.com/en/sql/language-manual/delta-merge-into.html)
659    Wildcard,
660}
661
662impl Display for MergeInsertKind {
663    fn fmt(&self, f: &mut fmt::Formatter) -> fmt::Result {
664        match self {
665            MergeInsertKind::Values(values) => {
666                write!(f, "{values}")
667            }
668            MergeInsertKind::Row => {
669                write!(f, "ROW")
670            }
671            MergeInsertKind::Wildcard => {
672                write!(f, "*")
673            }
674        }
675    }
676}
677
678/// The expression used to insert rows within a `MERGE` statement.
679///
680/// Examples
681/// ```sql
682/// INSERT (product, quantity) VALUES(product, quantity)
683/// INSERT ROW
684/// ```
685///
686/// [Snowflake](https://docs.snowflake.com/en/sql-reference/sql/merge)
687/// [BigQuery](https://cloud.google.com/bigquery/docs/reference/standard-sql/dml-syntax#merge_statement)
688/// [Oracle](https://docs.oracle.com/en/database/oracle/oracle-database/21/sqlrf/MERGE.html)
689#[derive(Debug, Clone, PartialEq, PartialOrd, Eq, Ord, Hash)]
690#[cfg_attr(feature = "serde", derive(Serialize, Deserialize))]
691#[cfg_attr(feature = "visitor", derive(Visit, VisitMut))]
692pub struct MergeInsertExpr {
693    /// The `INSERT` token that starts the sub-expression.
694    pub insert_token: AttachedToken,
695    /// Columns (if any) specified by the insert.
696    ///
697    /// Example:
698    /// ```sql
699    /// INSERT (product, quantity) VALUES(product, quantity)
700    /// INSERT (product, quantity) ROW
701    /// ```
702    pub columns: Vec<ObjectName>,
703    /// The token, `[VALUES | ROW]` starting `kind`.
704    pub kind_token: AttachedToken,
705    /// The insert type used by the statement.
706    pub kind: MergeInsertKind,
707    /// An optional condition to restrict the insertion (Oracle specific)
708    pub insert_predicate: Option<Expr>,
709}
710
711impl Display for MergeInsertExpr {
712    fn fmt(&self, f: &mut fmt::Formatter) -> fmt::Result {
713        if !self.columns.is_empty() {
714            write!(f, "({}) ", display_comma_separated(self.columns.as_slice()))?;
715        }
716        write!(f, "{}", self.kind)?;
717        if let Some(predicate) = self.insert_predicate.as_ref() {
718            write!(f, " WHERE {}", predicate)?;
719        }
720        Ok(())
721    }
722}
723
724/// The kind of update used within a `MERGE` statement.
725#[derive(Debug, Clone, PartialEq, PartialOrd, Eq, Ord, Hash)]
726#[cfg_attr(feature = "serde", derive(Serialize, Deserialize))]
727#[cfg_attr(feature = "visitor", derive(Visit, VisitMut))]
728pub enum MergeUpdateKind {
729    /// Standard update with explicit assignments.
730    ///
731    /// Example:
732    /// ```sql
733    /// UPDATE SET quantity = source.quantity, name = source.name
734    /// ```
735    Set(Vec<Assignment>),
736    /// The `*` wildcard to update all columns from the source.
737    ///
738    /// Example:
739    /// ```sql
740    /// UPDATE SET *
741    /// ```
742    /// [Databricks](https://docs.databricks.com/en/sql/language-manual/delta-merge-into.html)
743    Wildcard,
744}
745
746impl Display for MergeUpdateKind {
747    fn fmt(&self, f: &mut fmt::Formatter<'_>) -> fmt::Result {
748        match self {
749            MergeUpdateKind::Set(assignments) => {
750                write!(f, "SET {}", display_comma_separated(assignments))
751            }
752            MergeUpdateKind::Wildcard => {
753                write!(f, "SET *")
754            }
755        }
756    }
757}
758
759/// The expression used to update rows within a `MERGE` statement.
760///
761/// Examples
762/// ```sql
763/// UPDATE SET quantity = T.quantity + S.quantity
764/// UPDATE SET *
765/// ```
766///
767/// [Snowflake](https://docs.snowflake.com/en/sql-reference/sql/merge)
768/// [BigQuery](https://cloud.google.com/bigquery/docs/reference/standard-sql/dml-syntax#merge_statement)
769/// [Oracle](https://docs.oracle.com/en/database/oracle/oracle-database/21/sqlrf/MERGE.html)
770/// [Databricks](https://docs.databricks.com/en/sql/language-manual/delta-merge-into.html)
771#[derive(Debug, Clone, PartialEq, PartialOrd, Eq, Ord, Hash)]
772#[cfg_attr(feature = "serde", derive(Serialize, Deserialize))]
773#[cfg_attr(feature = "visitor", derive(Visit, VisitMut))]
774pub struct MergeUpdateExpr {
775    /// The `UPDATE` token that starts the sub-expression.
776    pub update_token: AttachedToken,
777    /// The kind of update: explicit assignments or `*` shorthand.
778    pub kind: MergeUpdateKind,
779    /// `where_clause` for the update (Oracle specific)
780    pub update_predicate: Option<Expr>,
781    /// `delete_clause` for the update "delete where" (Oracle specific)
782    pub delete_predicate: Option<Expr>,
783}
784
785impl Display for MergeUpdateExpr {
786    fn fmt(&self, f: &mut fmt::Formatter<'_>) -> fmt::Result {
787        write!(f, "{}", self.kind)?;
788        if let Some(predicate) = self.update_predicate.as_ref() {
789            write!(f, " WHERE {predicate}")?;
790        }
791        if let Some(predicate) = self.delete_predicate.as_ref() {
792            write!(f, " DELETE WHERE {predicate}")?;
793        }
794        Ok(())
795    }
796}
797
798/// An `OUTPUT` clause on `MERGE`, `INSERT`, `UPDATE`, or `DELETE` (MSSQL).
799///
800/// Example:
801/// OUTPUT $action, deleted.* INTO dbo.temp_products;
802/// <https://learn.microsoft.com/en-us/sql/t-sql/queries/output-clause-transact-sql>
803#[derive(Debug, Clone, PartialEq, PartialOrd, Eq, Ord, Hash)]
804#[cfg_attr(feature = "serde", derive(Serialize, Deserialize))]
805#[cfg_attr(feature = "visitor", derive(Visit, VisitMut))]
806pub enum OutputClause {
807    /// `OUTPUT` clause
808    Output {
809        /// The `OUTPUT` token that starts the sub-expression.
810        output_token: AttachedToken,
811        /// The select items to output
812        select_items: Vec<SelectItem>,
813        /// Optional `INTO` table to direct the output
814        into_table: Option<SelectInto>,
815    },
816    /// `RETURNING` clause
817    Returning {
818        /// The `RETURNING` token that starts the sub-expression.
819        returning_token: AttachedToken,
820        /// The select items to return
821        select_items: Vec<SelectItem>,
822    },
823}
824
825impl fmt::Display for OutputClause {
826    fn fmt(&self, f: &mut fmt::Formatter) -> fmt::Result {
827        match self {
828            OutputClause::Output {
829                output_token: _,
830                select_items,
831                into_table,
832            } => {
833                f.write_str("OUTPUT ")?;
834                display_comma_separated(select_items).fmt(f)?;
835                if let Some(into_table) = into_table {
836                    f.write_str(" ")?;
837                    into_table.fmt(f)?;
838                }
839                Ok(())
840            }
841            OutputClause::Returning {
842                returning_token: _,
843                select_items,
844            } => {
845                f.write_str("RETURNING ")?;
846                display_comma_separated(select_items).fmt(f)
847            }
848        }
849    }
850}
851
852/// A WHEN clause in a conditional multi-table INSERT.
853///
854/// Syntax:
855/// ```sql
856/// WHEN n1 > 100 THEN
857///   INTO t1
858///   INTO t2 (c1, c2) VALUES (n1, n2)
859/// ```
860#[derive(Debug, Clone, PartialEq, PartialOrd, Eq, Ord, Hash)]
861#[cfg_attr(feature = "serde", derive(Serialize, Deserialize))]
862#[cfg_attr(feature = "visitor", derive(Visit, VisitMut))]
863pub struct MultiTableInsertWhenClause {
864    /// The condition for this WHEN clause
865    pub condition: Expr,
866    /// The INTO clauses to execute when the condition is true
867    pub into_clauses: Vec<MultiTableInsertIntoClause>,
868}
869
870impl Display for MultiTableInsertWhenClause {
871    fn fmt(&self, f: &mut fmt::Formatter<'_>) -> fmt::Result {
872        write!(f, "WHEN {} THEN", self.condition)?;
873        for into_clause in &self.into_clauses {
874            SpaceOrNewline.fmt(f)?;
875            write!(f, "{}", into_clause)?;
876        }
877        Ok(())
878    }
879}
880
881/// An INTO clause in a multi-table INSERT.
882///
883/// Syntax:
884/// ```sql
885/// INTO <target_table> [ ( <target_col_name> [ , ... ] ) ] [ VALUES ( { <source_col_name> | DEFAULT | NULL } [ , ... ] ) ]
886/// ```
887#[derive(Debug, Clone, PartialEq, PartialOrd, Eq, Ord, Hash)]
888#[cfg_attr(feature = "serde", derive(Serialize, Deserialize))]
889#[cfg_attr(feature = "visitor", derive(Visit, VisitMut))]
890pub struct MultiTableInsertIntoClause {
891    /// The target table
892    pub table_name: ObjectName,
893    /// The target columns (optional)
894    pub columns: Vec<Ident>,
895    /// The VALUES clause (optional)
896    pub values: Option<MultiTableInsertValues>,
897}
898
899impl Display for MultiTableInsertIntoClause {
900    fn fmt(&self, f: &mut fmt::Formatter<'_>) -> fmt::Result {
901        write!(f, "INTO {}", self.table_name)?;
902        if !self.columns.is_empty() {
903            write!(f, " ({})", display_comma_separated(&self.columns))?;
904        }
905        if let Some(values) = &self.values {
906            write!(f, " VALUES ({})", display_comma_separated(&values.values))?;
907        }
908        Ok(())
909    }
910}
911
912/// The VALUES clause in a multi-table INSERT INTO clause.
913#[derive(Debug, Clone, PartialEq, PartialOrd, Eq, Ord, Hash)]
914#[cfg_attr(feature = "serde", derive(Serialize, Deserialize))]
915#[cfg_attr(feature = "visitor", derive(Visit, VisitMut))]
916pub struct MultiTableInsertValues {
917    /// The values to insert (can be column references, DEFAULT, or NULL)
918    pub values: Vec<MultiTableInsertValue>,
919}
920
921/// A value in a multi-table INSERT VALUES clause.
922#[derive(Debug, Clone, PartialEq, PartialOrd, Eq, Ord, Hash)]
923#[cfg_attr(feature = "serde", derive(Serialize, Deserialize))]
924#[cfg_attr(feature = "visitor", derive(Visit, VisitMut))]
925pub enum MultiTableInsertValue {
926    /// A column reference or expression from the source
927    Expr(Expr),
928    /// The DEFAULT keyword
929    Default,
930}
931
932impl Display for MultiTableInsertValue {
933    fn fmt(&self, f: &mut fmt::Formatter<'_>) -> fmt::Result {
934        match self {
935            MultiTableInsertValue::Expr(expr) => write!(f, "{}", expr),
936            MultiTableInsertValue::Default => write!(f, "DEFAULT"),
937        }
938    }
939}
940
941/// The type of multi-table INSERT statement(Snowflake).
942///
943/// See: <https://docs.snowflake.com/en/sql-reference/sql/insert-multi-table>
944#[derive(Debug, Clone, PartialEq, PartialOrd, Eq, Ord, Hash)]
945#[cfg_attr(feature = "serde", derive(Serialize, Deserialize))]
946#[cfg_attr(feature = "visitor", derive(Visit, VisitMut))]
947pub enum MultiTableInsertType {
948    /// `INSERT ALL` - all matching WHEN clauses are executed
949    All,
950    /// `INSERT FIRST` - only the first matching WHEN clause is executed
951    First,
952}
953
954impl Display for MultiTableInsertType {
955    fn fmt(&self, f: &mut fmt::Formatter<'_>) -> fmt::Result {
956        match self {
957            MultiTableInsertType::All => write!(f, "ALL"),
958            MultiTableInsertType::First => write!(f, "FIRST"),
959        }
960    }
961}
sqlparser/ast/dml.rs

sqlparser/ast/
dml.rs
