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