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