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