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