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

sqlparser/ast/
dml.rs
