sea_query/query/update.rs
1use inherent::inherent;
2use std::borrow::Cow;
3
4use crate::{
5 ConditionHolder, ConditionalStatement, DynIden, Expr, IntoColumnRef, IntoCondition, IntoIden,
6 IntoTableRef, LogicalChainOper, NullOrdering, Order, OrderExpr, OrderedStatement, QueryBuilder,
7 QueryStatement, QueryStatementBuilder, QueryStatementWriter, ReturningClause, SqlWriter,
8 SubQueryStatement, TableRef, Value, Values, WithClause, WithQuery,
9};
10
11/// Update existing rows in the table
12///
13/// # Examples
14///
15/// ```
16/// use sea_query::{tests_cfg::*, *};
17///
18/// let query = Query::update()
19/// .table(Glyph::Table)
20/// .values([(Glyph::Aspect, 1.23.into()), (Glyph::Image, "123".into())])
21/// .and_where(Expr::col(Glyph::Id).eq(1))
22/// .to_owned();
23///
24/// assert_eq!(
25/// query.to_string(MysqlQueryBuilder),
26/// r#"UPDATE `glyph` SET `aspect` = 1.23, `image` = '123' WHERE `id` = 1"#
27/// );
28/// assert_eq!(
29/// query.to_string(PostgresQueryBuilder),
30/// r#"UPDATE "glyph" SET "aspect" = 1.23, "image" = '123' WHERE "id" = 1"#
31/// );
32/// assert_eq!(
33/// query.to_string(SqliteQueryBuilder),
34/// r#"UPDATE "glyph" SET "aspect" = 1.23, "image" = '123' WHERE "id" = 1"#
35/// );
36/// ```
37#[derive(Default, Debug, Clone, PartialEq)]
38pub struct UpdateStatement {
39 pub(crate) table: Option<Box<TableRef>>,
40 pub(crate) from: Vec<TableRef>,
41 pub(crate) values: Vec<(DynIden, Box<Expr>)>,
42 pub(crate) r#where: ConditionHolder,
43 pub(crate) orders: Vec<OrderExpr>,
44 pub(crate) limit: Option<Value>,
45 pub(crate) returning: Option<ReturningClause>,
46 pub(crate) with: Option<WithClause>,
47}
48
49impl UpdateStatement {
50 /// Construct a new [`UpdateStatement`]
51 pub fn new() -> Self {
52 Self::default()
53 }
54
55 pub fn take(&mut self) -> Self {
56 Self {
57 table: self.table.take(),
58 from: std::mem::take(&mut self.from),
59 values: std::mem::take(&mut self.values),
60 r#where: std::mem::take(&mut self.r#where),
61 orders: std::mem::take(&mut self.orders),
62 limit: self.limit.take(),
63 returning: self.returning.take(),
64 with: self.with.take(),
65 }
66 }
67
68 /// Specify which table to update.
69 ///
70 /// # Examples
71 ///
72 /// See [`UpdateStatement::values`]
73 #[allow(clippy::wrong_self_convention)]
74 pub fn table<T>(&mut self, tbl_ref: T) -> &mut Self
75 where
76 T: IntoTableRef,
77 {
78 self.table = Some(Box::new(tbl_ref.into_table_ref()));
79 self
80 }
81
82 /// Update using data from another table (`UPDATE .. FROM ..`).
83 ///
84 /// # MySQL Notes
85 ///
86 /// MySQL doesn't support the UPDATE FROM syntax. And the current implementation attempt to tranform it to the UPDATE JOIN syntax,
87 /// which only works for one join target.
88 ///
89 /// # Examples
90 ///
91 /// ```
92 /// use sea_query::{audit::*, tests_cfg::*, *};
93 ///
94 /// let query = Query::update()
95 /// .table(Glyph::Table)
96 /// .value(Glyph::Tokens, Expr::column((Char::Table, Char::Character)))
97 /// .from(Char::Table)
98 /// .cond_where(
99 /// Expr::col((Glyph::Table, Glyph::Image))
100 /// .eq(Expr::col((Char::Table, Char::UserData))),
101 /// )
102 /// .to_owned();
103 ///
104 /// assert_eq!(
105 /// query.to_string(MysqlQueryBuilder),
106 /// "UPDATE `glyph` JOIN `character` ON `glyph`.`image` = `character`.`user_data` SET `glyph`.`tokens` = `character`.`character`"
107 /// );
108 /// assert_eq!(
109 /// query.to_string(PostgresQueryBuilder),
110 /// r#"UPDATE "glyph" SET "tokens" = "character"."character" FROM "character" WHERE "glyph"."image" = "character"."user_data""#
111 /// );
112 /// assert_eq!(
113 /// query.to_string(SqliteQueryBuilder),
114 /// r#"UPDATE "glyph" SET "tokens" = "character"."character" FROM "character" WHERE "glyph"."image" = "character"."user_data""#
115 /// );
116 /// assert_eq!(
117 /// query.audit().unwrap().updated_tables(),
118 /// [Glyph::Table.into_iden()]
119 /// );
120 /// assert_eq!(
121 /// query.audit().unwrap().selected_tables(),
122 /// [Char::Table.into_iden()]
123 /// );
124 /// ```
125 pub fn from<R>(&mut self, tbl_ref: R) -> &mut Self
126 where
127 R: IntoTableRef,
128 {
129 self.from_from(tbl_ref.into_table_ref())
130 }
131
132 #[allow(clippy::wrong_self_convention)]
133 fn from_from(&mut self, select: TableRef) -> &mut Self {
134 self.from.push(select);
135 self
136 }
137
138 /// Update column values. To set multiple column-value pairs at once.
139 ///
140 /// # Examples
141 ///
142 /// ```
143 /// use sea_query::{audit::*, tests_cfg::*, *};
144 ///
145 /// let query = Query::update()
146 /// .table(Glyph::Table)
147 /// .values([
148 /// (Glyph::Aspect, 2.1345.into()),
149 /// (Glyph::Image, "235m".into()),
150 /// ])
151 /// .to_owned();
152 ///
153 /// assert_eq!(
154 /// query.to_string(MysqlQueryBuilder),
155 /// r#"UPDATE `glyph` SET `aspect` = 2.1345, `image` = '235m'"#
156 /// );
157 /// assert_eq!(
158 /// query.to_string(PostgresQueryBuilder),
159 /// r#"UPDATE "glyph" SET "aspect" = 2.1345, "image" = '235m'"#
160 /// );
161 /// assert_eq!(
162 /// query.to_string(SqliteQueryBuilder),
163 /// r#"UPDATE "glyph" SET "aspect" = 2.1345, "image" = '235m'"#
164 /// );
165 /// assert_eq!(
166 /// query.audit().unwrap().updated_tables(),
167 /// [Glyph::Table.into_iden()]
168 /// );
169 /// assert_eq!(query.audit().unwrap().selected_tables(), []);
170 /// ```
171 pub fn values<T, I>(&mut self, values: I) -> &mut Self
172 where
173 T: IntoIden,
174 I: IntoIterator<Item = (T, Expr)>,
175 {
176 for (k, v) in values.into_iter() {
177 self.values.push((k.into_iden(), Box::new(v)));
178 }
179 self
180 }
181
182 /// Update column value by [`Expr`].
183 ///
184 /// # Examples
185 ///
186 /// ```
187 /// use sea_query::{*, tests_cfg::*};
188 ///
189 /// let query = Query::update()
190 /// .table(Glyph::Table)
191 /// .value(Glyph::Aspect, Expr::cust("60 * 24 * 24"))
192 /// .values([
193 /// (Glyph::Image, "24B0E11951B03B07F8300FD003983F03F0780060".into()),
194 /// ])
195 /// .to_owned();
196 ///
197 /// assert_eq!(
198 /// query.to_string(MysqlQueryBuilder),
199 /// r#"UPDATE `glyph` SET `aspect` = 60 * 24 * 24, `image` = '24B0E11951B03B07F8300FD003983F03F0780060'"#
200 /// );
201 /// assert_eq!(
202 /// query.to_string(PostgresQueryBuilder),
203 /// r#"UPDATE "glyph" SET "aspect" = 60 * 24 * 24, "image" = '24B0E11951B03B07F8300FD003983F03F0780060'"#
204 /// );
205 /// assert_eq!(
206 /// query.to_string(SqliteQueryBuilder),
207 /// r#"UPDATE "glyph" SET "aspect" = 60 * 24 * 24, "image" = '24B0E11951B03B07F8300FD003983F03F0780060'"#
208 /// );
209 ///
210 /// let query = Query::update()
211 /// .table(Glyph::Table)
212 /// .value(Glyph::Aspect, Expr::value(Value::Int(None)))
213 /// .to_owned();
214 ///
215 /// assert_eq!(
216 /// query.to_string(MysqlQueryBuilder),
217 /// r#"UPDATE `glyph` SET `aspect` = NULL"#
218 /// );
219 /// assert_eq!(
220 /// query.to_string(PostgresQueryBuilder),
221 /// r#"UPDATE "glyph" SET "aspect" = NULL"#
222 /// );
223 /// assert_eq!(
224 /// query.to_string(SqliteQueryBuilder),
225 /// r#"UPDATE "glyph" SET "aspect" = NULL"#
226 /// );
227 /// ```
228 pub fn value<C, T>(&mut self, col: C, value: T) -> &mut Self
229 where
230 C: IntoIden,
231 T: Into<Expr>,
232 {
233 self.values.push((col.into_iden(), Box::new(value.into())));
234 self
235 }
236
237 /// Limit number of updated rows.
238 pub fn limit(&mut self, limit: u64) -> &mut Self {
239 self.limit = Some(limit.into());
240 self
241 }
242
243 /// RETURNING expressions.
244 ///
245 /// # Examples
246 ///
247 /// ```
248 /// use sea_query::{audit::*, tests_cfg::*, *};
249 ///
250 /// let query = Query::update()
251 /// .table(Glyph::Table)
252 /// .value(Glyph::Aspect, 2.1345)
253 /// .value(Glyph::Image, "235m")
254 /// .returning(Query::returning().columns([Glyph::Id]))
255 /// .to_owned();
256 ///
257 /// assert_eq!(
258 /// query.to_string(MysqlQueryBuilder),
259 /// r#"UPDATE `glyph` SET `aspect` = 2.1345, `image` = '235m'"#
260 /// );
261 /// assert_eq!(
262 /// query.to_string(PostgresQueryBuilder),
263 /// r#"UPDATE "glyph" SET "aspect" = 2.1345, "image" = '235m' RETURNING "id""#
264 /// );
265 /// assert_eq!(
266 /// query.to_string(SqliteQueryBuilder),
267 /// r#"UPDATE "glyph" SET "aspect" = 2.1345, "image" = '235m' RETURNING "id""#
268 /// );
269 /// assert_eq!(
270 /// query.audit().unwrap().updated_tables(),
271 /// [Glyph::Table.into_iden()]
272 /// );
273 /// assert_eq!(
274 /// query.audit().unwrap().selected_tables(),
275 /// [Glyph::Table.into_iden()]
276 /// );
277 /// ```
278 pub fn returning(&mut self, returning: ReturningClause) -> &mut Self {
279 self.returning = Some(returning);
280 self
281 }
282
283 /// RETURNING expressions for a column.
284 ///
285 /// # Examples
286 ///
287 /// ```
288 /// use sea_query::{tests_cfg::*, *};
289 ///
290 /// let query = Query::update()
291 /// .table(Glyph::Table)
292 /// .table(Glyph::Table)
293 /// .value(Glyph::Aspect, 2.1345)
294 /// .value(Glyph::Image, "235m")
295 /// .returning_col(Glyph::Id)
296 /// .to_owned();
297 ///
298 /// assert_eq!(
299 /// query.to_string(MysqlQueryBuilder),
300 /// r#"UPDATE `glyph` SET `aspect` = 2.1345, `image` = '235m'"#
301 /// );
302 /// assert_eq!(
303 /// query.to_string(PostgresQueryBuilder),
304 /// r#"UPDATE "glyph" SET "aspect" = 2.1345, "image" = '235m' RETURNING "id""#
305 /// );
306 /// assert_eq!(
307 /// query.to_string(SqliteQueryBuilder),
308 /// r#"UPDATE "glyph" SET "aspect" = 2.1345, "image" = '235m' RETURNING "id""#
309 /// );
310 /// ```
311 pub fn returning_col<C>(&mut self, col: C) -> &mut Self
312 where
313 C: IntoColumnRef,
314 {
315 self.returning(ReturningClause::Columns(vec![col.into_column_ref()]))
316 }
317
318 /// RETURNING expressions all columns.
319 ///
320 /// # Examples
321 ///
322 /// ```
323 /// use sea_query::{tests_cfg::*, *};
324 ///
325 /// let query = Query::update()
326 /// .table(Glyph::Table)
327 /// .table(Glyph::Table)
328 /// .value(Glyph::Aspect, 2.1345)
329 /// .value(Glyph::Image, "235m")
330 /// .returning_all()
331 /// .to_owned();
332 ///
333 /// assert_eq!(
334 /// query.to_string(MysqlQueryBuilder),
335 /// r#"UPDATE `glyph` SET `aspect` = 2.1345, `image` = '235m'"#
336 /// );
337 /// assert_eq!(
338 /// query.to_string(PostgresQueryBuilder),
339 /// r#"UPDATE "glyph" SET "aspect" = 2.1345, "image" = '235m' RETURNING *"#
340 /// );
341 /// assert_eq!(
342 /// query.to_string(SqliteQueryBuilder),
343 /// r#"UPDATE "glyph" SET "aspect" = 2.1345, "image" = '235m' RETURNING *"#
344 /// );
345 /// ```
346 pub fn returning_all(&mut self) -> &mut Self {
347 self.returning(ReturningClause::All)
348 }
349
350 /// Create a [WithQuery] by specifying a [WithClause] to execute this query with.
351 ///
352 /// # Examples
353 ///
354 /// ```
355 /// use sea_query::{IntoCondition, IntoIden, audit::*, tests_cfg::*, *};
356 ///
357 /// let select = SelectStatement::new()
358 /// .columns([Glyph::Id])
359 /// .from(Glyph::Table)
360 /// .and_where(Expr::col(Glyph::Image).like("0%"))
361 /// .to_owned();
362 /// let cte = CommonTableExpression::new()
363 /// .query(select)
364 /// .column(Glyph::Id)
365 /// .table_name("cte")
366 /// .to_owned();
367 /// let with_clause = WithClause::new().cte(cte).to_owned();
368 /// let update = UpdateStatement::new()
369 /// .table(Glyph::Table)
370 /// .and_where(Expr::col(Glyph::Id).in_subquery(SelectStatement::new().column(Glyph::Id).from("cte").to_owned()))
371 /// .value(Glyph::Aspect, Expr::cust("60 * 24 * 24"))
372 /// .to_owned();
373 /// let query = update.with(with_clause);
374 ///
375 /// assert_eq!(
376 /// query.to_string(MysqlQueryBuilder),
377 /// r#"WITH `cte` (`id`) AS (SELECT `id` FROM `glyph` WHERE `image` LIKE '0%') UPDATE `glyph` SET `aspect` = 60 * 24 * 24 WHERE `id` IN (SELECT `id` FROM `cte`)"#
378 /// );
379 /// assert_eq!(
380 /// query.to_string(PostgresQueryBuilder),
381 /// r#"WITH "cte" ("id") AS (SELECT "id" FROM "glyph" WHERE "image" LIKE '0%') UPDATE "glyph" SET "aspect" = 60 * 24 * 24 WHERE "id" IN (SELECT "id" FROM "cte")"#
382 /// );
383 /// assert_eq!(
384 /// query.to_string(SqliteQueryBuilder),
385 /// r#"WITH "cte" ("id") AS (SELECT "id" FROM "glyph" WHERE "image" LIKE '0%') UPDATE "glyph" SET "aspect" = 60 * 24 * 24 WHERE "id" IN (SELECT "id" FROM "cte")"#
386 /// );
387 /// assert_eq!(
388 /// query.audit_unwrap().updated_tables(),
389 /// [Glyph::Table.into_iden()]
390 /// );
391 /// assert_eq!(
392 /// query.audit_unwrap().selected_tables(),
393 /// [Glyph::Table.into_iden()]
394 /// );
395 /// ```
396 pub fn with(self, clause: WithClause) -> WithQuery {
397 clause.query(self)
398 }
399
400 /// Create a Common Table Expression by specifying a [CommonTableExpression] or [WithClause] to execute this query with.
401 ///
402 /// # Examples
403 ///
404 /// ```
405 /// use sea_query::{IntoCondition, IntoIden, audit::*, tests_cfg::*, *};
406 ///
407 /// let select = SelectStatement::new()
408 /// .columns([Glyph::Id])
409 /// .from(Glyph::Table)
410 /// .and_where(Expr::col(Glyph::Image).like("0%"))
411 /// .to_owned();
412 /// let cte = CommonTableExpression::new()
413 /// .query(select)
414 /// .column(Glyph::Id)
415 /// .table_name("cte")
416 /// .to_owned();
417 /// let with_clause = WithClause::new().cte(cte).to_owned();
418 /// let query = UpdateStatement::new()
419 /// .table(Glyph::Table)
420 /// .and_where(Expr::col(Glyph::Id).in_subquery(SelectStatement::new().column(Glyph::Id).from("cte").to_owned()))
421 /// .value(Glyph::Aspect, Expr::cust("60 * 24 * 24"))
422 /// .with_cte(with_clause)
423 /// .to_owned();
424 ///
425 /// assert_eq!(
426 /// query.to_string(MysqlQueryBuilder),
427 /// r#"WITH `cte` (`id`) AS (SELECT `id` FROM `glyph` WHERE `image` LIKE '0%') UPDATE `glyph` SET `aspect` = 60 * 24 * 24 WHERE `id` IN (SELECT `id` FROM `cte`)"#
428 /// );
429 /// assert_eq!(
430 /// query.to_string(PostgresQueryBuilder),
431 /// r#"WITH "cte" ("id") AS (SELECT "id" FROM "glyph" WHERE "image" LIKE '0%') UPDATE "glyph" SET "aspect" = 60 * 24 * 24 WHERE "id" IN (SELECT "id" FROM "cte")"#
432 /// );
433 /// assert_eq!(
434 /// query.to_string(SqliteQueryBuilder),
435 /// r#"WITH "cte" ("id") AS (SELECT "id" FROM "glyph" WHERE "image" LIKE '0%') UPDATE "glyph" SET "aspect" = 60 * 24 * 24 WHERE "id" IN (SELECT "id" FROM "cte")"#
436 /// );
437 /// assert_eq!(
438 /// query.audit_unwrap().updated_tables(),
439 /// [Glyph::Table.into_iden()]
440 /// );
441 /// assert_eq!(
442 /// query.audit_unwrap().selected_tables(),
443 /// [Glyph::Table.into_iden()]
444 /// );
445 /// ```
446 pub fn with_cte<C: Into<WithClause>>(&mut self, clause: C) -> &mut Self {
447 self.with = Some(clause.into());
448 self
449 }
450
451 /// Get column values
452 pub fn get_values(&self) -> &[(DynIden, Box<Expr>)] {
453 &self.values
454 }
455}
456
457#[inherent]
458impl QueryStatementBuilder for UpdateStatement {
459 pub fn build_collect_any_into(
460 &self,
461 query_builder: &impl QueryBuilder,
462 sql: &mut impl SqlWriter,
463 ) {
464 query_builder.prepare_update_statement(self, sql);
465 }
466
467 pub fn build_any(&self, query_builder: &impl QueryBuilder) -> (String, Values);
468 pub fn build_collect_any(
469 &self,
470 query_builder: &impl QueryBuilder,
471 sql: &mut impl SqlWriter,
472 ) -> String;
473}
474
475impl From<UpdateStatement> for QueryStatement {
476 fn from(s: UpdateStatement) -> Self {
477 Self::Update(s)
478 }
479}
480
481impl From<UpdateStatement> for SubQueryStatement {
482 fn from(s: UpdateStatement) -> Self {
483 Self::UpdateStatement(s)
484 }
485}
486
487#[inherent]
488impl QueryStatementWriter for UpdateStatement {
489 pub fn build_collect_into<T: QueryBuilder>(&self, query_builder: T, sql: &mut impl SqlWriter) {
490 query_builder.prepare_update_statement(self, sql);
491 }
492
493 pub fn build_collect<T: QueryBuilder>(
494 &self,
495 query_builder: T,
496 sql: &mut impl SqlWriter,
497 ) -> String;
498 pub fn build<T: QueryBuilder>(&self, query_builder: T) -> (String, Values);
499 pub fn to_string<T: QueryBuilder>(&self, query_builder: T) -> String;
500}
501
502#[inherent]
503impl OrderedStatement for UpdateStatement {
504 pub fn add_order_by(&mut self, order: OrderExpr) -> &mut Self {
505 self.orders.push(order);
506 self
507 }
508
509 pub fn clear_order_by(&mut self) -> &mut Self {
510 self.orders = Vec::new();
511 self
512 }
513 pub fn order_by<T>(&mut self, col: T, order: Order) -> &mut Self
514 where
515 T: IntoColumnRef;
516
517 pub fn order_by_expr(&mut self, expr: Expr, order: Order) -> &mut Self;
518 pub fn order_by_customs<I, T>(&mut self, cols: I) -> &mut Self
519 where
520 T: Into<Cow<'static, str>>,
521 I: IntoIterator<Item = (T, Order)>;
522 pub fn order_by_columns<I, T>(&mut self, cols: I) -> &mut Self
523 where
524 T: IntoColumnRef,
525 I: IntoIterator<Item = (T, Order)>;
526 pub fn order_by_with_nulls<T>(
527 &mut self,
528 col: T,
529 order: Order,
530 nulls: NullOrdering,
531 ) -> &mut Self
532 where
533 T: IntoColumnRef;
534 pub fn order_by_expr_with_nulls(
535 &mut self,
536 expr: Expr,
537 order: Order,
538 nulls: NullOrdering,
539 ) -> &mut Self;
540 pub fn order_by_customs_with_nulls<I, T>(&mut self, cols: I) -> &mut Self
541 where
542 T: Into<Cow<'static, str>>,
543 I: IntoIterator<Item = (T, Order, NullOrdering)>;
544 pub fn order_by_columns_with_nulls<I, T>(&mut self, cols: I) -> &mut Self
545 where
546 T: IntoColumnRef,
547 I: IntoIterator<Item = (T, Order, NullOrdering)>;
548}
549
550#[inherent]
551impl ConditionalStatement for UpdateStatement {
552 pub fn and_or_where(&mut self, condition: LogicalChainOper) -> &mut Self {
553 self.r#where.add_and_or(condition);
554 self
555 }
556
557 pub fn cond_where<C>(&mut self, condition: C) -> &mut Self
558 where
559 C: IntoCondition,
560 {
561 self.r#where.add_condition(condition.into_condition());
562 self
563 }
564
565 pub fn and_where_option(&mut self, other: Option<Expr>) -> &mut Self;
566 pub fn and_where(&mut self, other: Expr) -> &mut Self;
567}