Struct UpdateStatement

pub struct UpdateStatement {
    pub(crate) table: Option<Box<TableRef>>,
    pub(crate) from: Vec<TableRef>,
    pub(crate) values: Vec<(DynIden, Box<Expr>)>,
    pub(crate) where: ConditionHolder,
    pub(crate) orders: Vec<OrderExpr>,
    pub(crate) limit: Option<Value>,
    pub(crate) returning: Option<ReturningClause>,
    pub(crate) with: Option<WithClause>,
}

Update existing rows in the table

Examples

use sea_query::{tests_cfg::*, *};

let query = Query::update()
    .table(Glyph::Table)
    .values([(Glyph::Aspect, 1.23.into()), (Glyph::Image, "123".into())])
    .and_where(Expr::col(Glyph::Id).eq(1))
    .to_owned();

assert_eq!(
    query.to_string(MysqlQueryBuilder),
    r#"UPDATE `glyph` SET `aspect` = 1.23, `image` = '123' WHERE `id` = 1"#
);
assert_eq!(
    query.to_string(PostgresQueryBuilder),
    r#"UPDATE "glyph" SET "aspect" = 1.23, "image" = '123' WHERE "id" = 1"#
);
assert_eq!(
    query.to_string(SqliteQueryBuilder),
    r#"UPDATE "glyph" SET "aspect" = 1.23, "image" = '123' WHERE "id" = 1"#
);

Fields

table: Option<Box<TableRef>>

from: Vec<TableRef>

values: Vec<(DynIden, Box<Expr>)>

where: ConditionHolder

orders: Vec<OrderExpr>

limit: Option<Value>

returning: Option<ReturningClause>

with: Option<WithClause>

Implementations

impl UpdateStatement

pub fn new() -> Self

Construct a new UpdateStatement

pub fn take(&mut self) -> Self

pub fn table<T>(&mut self, tbl_ref: T) -> &mut Self

where T: IntoTableRef,

Specify which table to update.

Examples

See UpdateStatement::values

pub fn from<R>(&mut self, tbl_ref: R) -> &mut Self

where R: IntoTableRef,

Update using data from another table (UPDATE .. FROM ..).

MySQL Notes

MySQL doesn’t support the UPDATE FROM syntax. And the current implementation attempt to tranform it to the UPDATE JOIN syntax, which only works for one join target.

Examples

use sea_query::{audit::*, tests_cfg::*, *};

let query = Query::update()
    .table(Glyph::Table)
    .value(Glyph::Tokens, Expr::column((Char::Table, Char::Character)))
    .from(Char::Table)
    .cond_where(
        Expr::col((Glyph::Table, Glyph::Image))
            .eq(Expr::col((Char::Table, Char::UserData))),
    )
    .to_owned();

assert_eq!(
    query.to_string(MysqlQueryBuilder),
    "UPDATE `glyph` JOIN `character` ON `glyph`.`image` = `character`.`user_data` SET `glyph`.`tokens` = `character`.`character`"
);
assert_eq!(
    query.to_string(PostgresQueryBuilder),
    r#"UPDATE "glyph" SET "tokens" = "character"."character" FROM "character" WHERE "glyph"."image" = "character"."user_data""#
);
assert_eq!(
    query.to_string(SqliteQueryBuilder),
    r#"UPDATE "glyph" SET "tokens" = "character"."character" FROM "character" WHERE "glyph"."image" = "character"."user_data""#
);
assert_eq!(
    query.audit().unwrap().updated_tables(),
    [Glyph::Table.into_iden()]
);
assert_eq!(
    query.audit().unwrap().selected_tables(),
    [Char::Table.into_iden()]
);

fn from_from(&mut self, select: TableRef) -> &mut Self

pub fn values<T, I>(&mut self, values: I) -> &mut Self

where T: IntoIden, I: IntoIterator<Item = (T, Expr)>,

Update column values. To set multiple column-value pairs at once.

Examples

use sea_query::{audit::*, tests_cfg::*, *};

let query = Query::update()
    .table(Glyph::Table)
    .values([
        (Glyph::Aspect, 2.1345.into()),
        (Glyph::Image, "235m".into()),
    ])
    .to_owned();

assert_eq!(
    query.to_string(MysqlQueryBuilder),
    r#"UPDATE `glyph` SET `aspect` = 2.1345, `image` = '235m'"#
);
assert_eq!(
    query.to_string(PostgresQueryBuilder),
    r#"UPDATE "glyph" SET "aspect" = 2.1345, "image" = '235m'"#
);
assert_eq!(
    query.to_string(SqliteQueryBuilder),
    r#"UPDATE "glyph" SET "aspect" = 2.1345, "image" = '235m'"#
);
assert_eq!(
    query.audit().unwrap().updated_tables(),
    [Glyph::Table.into_iden()]
);
assert_eq!(query.audit().unwrap().selected_tables(), []);

pub fn value<C, T>(&mut self, col: C, value: T) -> &mut Self

where C: IntoIden, T: Into<Expr>,

Update column value by Expr.

Examples

use sea_query::{*, tests_cfg::*};

let query = Query::update()
    .table(Glyph::Table)
    .value(Glyph::Aspect, Expr::cust("60 * 24 * 24"))
    .values([
        (Glyph::Image, "24B0E11951B03B07F8300FD003983F03F0780060".into()),
    ])
    .to_owned();

assert_eq!(
    query.to_string(MysqlQueryBuilder),
    r#"UPDATE `glyph` SET `aspect` = 60 * 24 * 24, `image` = '24B0E11951B03B07F8300FD003983F03F0780060'"#
);
assert_eq!(
    query.to_string(PostgresQueryBuilder),
    r#"UPDATE "glyph" SET "aspect" = 60 * 24 * 24, "image" = '24B0E11951B03B07F8300FD003983F03F0780060'"#
);
assert_eq!(
    query.to_string(SqliteQueryBuilder),
    r#"UPDATE "glyph" SET "aspect" = 60 * 24 * 24, "image" = '24B0E11951B03B07F8300FD003983F03F0780060'"#
);

let query = Query::update()
    .table(Glyph::Table)
    .value(Glyph::Aspect, Expr::value(Value::Int(None)))
    .to_owned();

assert_eq!(
    query.to_string(MysqlQueryBuilder),
    r#"UPDATE `glyph` SET `aspect` = NULL"#
);
assert_eq!(
    query.to_string(PostgresQueryBuilder),
    r#"UPDATE "glyph" SET "aspect" = NULL"#
);
assert_eq!(
    query.to_string(SqliteQueryBuilder),
    r#"UPDATE "glyph" SET "aspect" = NULL"#
);

pub fn limit(&mut self, limit: u64) -> &mut Self

Limit number of updated rows.

pub fn returning(&mut self, returning: ReturningClause) -> &mut Self

RETURNING expressions.

Examples

use sea_query::{audit::*, tests_cfg::*, *};

let query = Query::update()
    .table(Glyph::Table)
    .value(Glyph::Aspect, 2.1345)
    .value(Glyph::Image, "235m")
    .returning(Query::returning().columns([Glyph::Id]))
    .to_owned();

assert_eq!(
    query.to_string(MysqlQueryBuilder),
    r#"UPDATE `glyph` SET `aspect` = 2.1345, `image` = '235m'"#
);
assert_eq!(
    query.to_string(PostgresQueryBuilder),
    r#"UPDATE "glyph" SET "aspect" = 2.1345, "image" = '235m' RETURNING "id""#
);
assert_eq!(
    query.to_string(SqliteQueryBuilder),
    r#"UPDATE "glyph" SET "aspect" = 2.1345, "image" = '235m' RETURNING "id""#
);
assert_eq!(
    query.audit().unwrap().updated_tables(),
    [Glyph::Table.into_iden()]
);
assert_eq!(
    query.audit().unwrap().selected_tables(),
    [Glyph::Table.into_iden()]
);

pub fn returning_col<C>(&mut self, col: C) -> &mut Self

where C: IntoColumnRef,

RETURNING expressions for a column.

Examples

use sea_query::{tests_cfg::*, *};

let query = Query::update()
    .table(Glyph::Table)
    .table(Glyph::Table)
    .value(Glyph::Aspect, 2.1345)
    .value(Glyph::Image, "235m")
    .returning_col(Glyph::Id)
    .to_owned();

assert_eq!(
    query.to_string(MysqlQueryBuilder),
    r#"UPDATE `glyph` SET `aspect` = 2.1345, `image` = '235m'"#
);
assert_eq!(
    query.to_string(PostgresQueryBuilder),
    r#"UPDATE "glyph" SET "aspect" = 2.1345, "image" = '235m' RETURNING "id""#
);
assert_eq!(
    query.to_string(SqliteQueryBuilder),
    r#"UPDATE "glyph" SET "aspect" = 2.1345, "image" = '235m' RETURNING "id""#
);

pub fn returning_all(&mut self) -> &mut Self

RETURNING expressions all columns.

Examples

use sea_query::{tests_cfg::*, *};

let query = Query::update()
    .table(Glyph::Table)
    .table(Glyph::Table)
    .value(Glyph::Aspect, 2.1345)
    .value(Glyph::Image, "235m")
    .returning_all()
    .to_owned();

assert_eq!(
    query.to_string(MysqlQueryBuilder),
    r#"UPDATE `glyph` SET `aspect` = 2.1345, `image` = '235m'"#
);
assert_eq!(
    query.to_string(PostgresQueryBuilder),
    r#"UPDATE "glyph" SET "aspect" = 2.1345, "image" = '235m' RETURNING *"#
);
assert_eq!(
    query.to_string(SqliteQueryBuilder),
    r#"UPDATE "glyph" SET "aspect" = 2.1345, "image" = '235m' RETURNING *"#
);

pub fn with(self, clause: WithClause) -> WithQuery

Create a WithQuery by specifying a WithClause to execute this query with.

Examples

use sea_query::{IntoCondition, IntoIden, audit::*, tests_cfg::*, *};

let select = SelectStatement::new()
        .columns([Glyph::Id])
        .from(Glyph::Table)
        .and_where(Expr::col(Glyph::Image).like("0%"))
        .to_owned();
    let cte = CommonTableExpression::new()
        .query(select)
        .column(Glyph::Id)
        .table_name("cte")
        .to_owned();
    let with_clause = WithClause::new().cte(cte).to_owned();
    let update = UpdateStatement::new()
        .table(Glyph::Table)
        .and_where(Expr::col(Glyph::Id).in_subquery(SelectStatement::new().column(Glyph::Id).from("cte").to_owned()))
        .value(Glyph::Aspect, Expr::cust("60 * 24 * 24"))
        .to_owned();
    let query = update.with(with_clause);

assert_eq!(
    query.to_string(MysqlQueryBuilder),
    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`)"#
);
assert_eq!(
    query.to_string(PostgresQueryBuilder),
    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")"#
);
assert_eq!(
    query.to_string(SqliteQueryBuilder),
    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")"#
);
assert_eq!(
    query.audit_unwrap().updated_tables(),
    [Glyph::Table.into_iden()]
);
assert_eq!(
    query.audit_unwrap().selected_tables(),
    [Glyph::Table.into_iden()]
);

pub fn with_cte<C: Into<WithClause>>(&mut self, clause: C) -> &mut Self

Create a Common Table Expression by specifying a [CommonTableExpression] or WithClause to execute this query with.

Examples

use sea_query::{IntoCondition, IntoIden, audit::*, tests_cfg::*, *};

let select = SelectStatement::new()
        .columns([Glyph::Id])
        .from(Glyph::Table)
        .and_where(Expr::col(Glyph::Image).like("0%"))
        .to_owned();
    let cte = CommonTableExpression::new()
        .query(select)
        .column(Glyph::Id)
        .table_name("cte")
        .to_owned();
    let with_clause = WithClause::new().cte(cte).to_owned();
    let query = UpdateStatement::new()
        .table(Glyph::Table)
        .and_where(Expr::col(Glyph::Id).in_subquery(SelectStatement::new().column(Glyph::Id).from("cte").to_owned()))
        .value(Glyph::Aspect, Expr::cust("60 * 24 * 24"))
        .with_cte(with_clause)
        .to_owned();

assert_eq!(
    query.to_string(MysqlQueryBuilder),
    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`)"#
);
assert_eq!(
    query.to_string(PostgresQueryBuilder),
    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")"#
);
assert_eq!(
    query.to_string(SqliteQueryBuilder),
    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")"#
);
assert_eq!(
    query.audit_unwrap().updated_tables(),
    [Glyph::Table.into_iden()]
);
assert_eq!(
    query.audit_unwrap().selected_tables(),
    [Glyph::Table.into_iden()]
);

pub fn get_values(&self) -> &[(DynIden, Box<Expr>)]

Get column values

impl UpdateStatement

pub fn build_collect_any_into( &self, query_builder: &dyn QueryBuilder, sql: &mut dyn SqlWriter, )

See QueryStatementBuilder::build_collect_any_into

pub fn build_any(&self, query_builder: &dyn QueryBuilder) -> (String, Values)

See QueryStatementBuilder::build_any

pub fn build_collect_any( &self, query_builder: &dyn QueryBuilder, sql: &mut dyn SqlWriter, ) -> String

See QueryStatementBuilder::build_collect_any

impl UpdateStatement

pub fn build_collect_into<T: QueryBuilder>( &self, query_builder: T, sql: &mut dyn SqlWriter, )

See QueryStatementWriter::build_collect_into

pub fn build_collect<T: QueryBuilder>( &self, query_builder: T, sql: &mut dyn SqlWriter, ) -> String

See QueryStatementWriter::build_collect

pub fn build<T: QueryBuilder>(&self, query_builder: T) -> (String, Values)

See QueryStatementWriter::build

pub fn to_string<T: QueryBuilder>(&self, query_builder: T) -> String

See QueryStatementWriter::to_string

impl UpdateStatement

pub fn add_order_by(&mut self, order: OrderExpr) -> &mut Self

See OrderedStatement::add_order_by

pub fn clear_order_by(&mut self) -> &mut Self

See OrderedStatement::clear_order_by

pub fn order_by<T>(&mut self, col: T, order: Order) -> &mut Self

where T: IntoColumnRef,

See OrderedStatement::order_by

pub fn order_by_expr(&mut self, expr: Expr, order: Order) -> &mut Self

See OrderedStatement::order_by_expr

pub fn order_by_customs<I, T>(&mut self, cols: I) -> &mut Self

where T: ToString, I: IntoIterator<Item = (T, Order)>,

See OrderedStatement::order_by_customs

pub fn order_by_columns<I, T>(&mut self, cols: I) -> &mut Self

where T: IntoColumnRef, I: IntoIterator<Item = (T, Order)>,

See OrderedStatement::order_by_columns

pub fn order_by_with_nulls<T>( &mut self, col: T, order: Order, nulls: NullOrdering, ) -> &mut Self

where T: IntoColumnRef,

See OrderedStatement::order_by_with_nulls

pub fn order_by_expr_with_nulls( &mut self, expr: Expr, order: Order, nulls: NullOrdering, ) -> &mut Self

See OrderedStatement::order_by_expr_with_nulls

pub fn order_by_customs_with_nulls<I, T>(&mut self, cols: I) -> &mut Self

where T: ToString, I: IntoIterator<Item = (T, Order, NullOrdering)>,

See OrderedStatement::order_by_customs_with_nulls

pub fn order_by_columns_with_nulls<I, T>(&mut self, cols: I) -> &mut Self

where T: IntoColumnRef, I: IntoIterator<Item = (T, Order, NullOrdering)>,

See OrderedStatement::order_by_columns_with_nulls

impl UpdateStatement

pub fn and_or_where(&mut self, condition: LogicalChainOper) -> &mut Self

See ConditionalStatement::and_or_where

pub fn cond_where<C>(&mut self, condition: C) -> &mut Self

where C: IntoCondition,

See ConditionalStatement::cond_where

pub fn and_where_option(&mut self, other: Option<Expr>) -> &mut Self

See ConditionalStatement::and_where_option

pub fn and_where(&mut self, other: Expr) -> &mut Self

See ConditionalStatement::and_where

Trait Implementations

impl Clone for UpdateStatement

fn clone(&self) -> UpdateStatement

Returns a duplicate of the value.

fn clone_from(&mut self, source: &Self)

Performs copy-assignment from source.

impl ConditionalStatement for UpdateStatement

fn cond_where<C>(&mut self, condition: C) -> &mut Self

where C: IntoCondition,

Where condition, expressed with any and all. Calling cond_where multiple times will conjoin them. Calling or_where after cond_where will panic.

fn and_where(&mut self, other: Expr) -> &mut Self

And where condition. Calling or_where after and_where will panic.

fn and_where_option(&mut self, other: Option<Expr>) -> &mut Self

Optional and where, short hand for if c.is_some() q.and_where(c).

impl Debug for UpdateStatement

fn fmt(&self, f: &mut Formatter<'_>) -> Result

Formats the value using the given formatter.

impl Default for UpdateStatement

fn default() -> UpdateStatement

Returns the “default value” for a type.

impl From<UpdateStatement> for QueryStatement

fn from(s: UpdateStatement) -> Self

Converts to this type from the input type.

impl From<UpdateStatement> for SubQueryStatement

fn from(s: UpdateStatement) -> Self

Converts to this type from the input type.

impl OrderedStatement for UpdateStatement

fn clear_order_by(&mut self) -> &mut Self

Clear order expressions

fn order_by<T>(&mut self, col: T, order: Order) -> &mut Self

where T: IntoColumnRef,

Order by column.

fn order_by_expr(&mut self, expr: Expr, order: Order) -> &mut Self

Order by Expr.

fn order_by_customs<I, T>(&mut self, cols: I) -> &mut Self

where T: ToString, I: IntoIterator<Item = (T, Order)>,

Order by custom string.

fn order_by_columns<I, T>(&mut self, cols: I) -> &mut Self

where T: IntoColumnRef, I: IntoIterator<Item = (T, Order)>,

Order by vector of columns.

fn order_by_with_nulls<T>( &mut self, col: T, order: Order, nulls: NullOrdering, ) -> &mut Self

where T: IntoColumnRef,

Order by column with nulls order option.

fn order_by_expr_with_nulls( &mut self, expr: Expr, order: Order, nulls: NullOrdering, ) -> &mut Self

Order by Expr with nulls order option.

fn order_by_customs_with_nulls<I, T>(&mut self, cols: I) -> &mut Self

where T: ToString, I: IntoIterator<Item = (T, Order, NullOrdering)>,

Order by custom string with nulls order option.

fn order_by_columns_with_nulls<I, T>(&mut self, cols: I) -> &mut Self

where T: IntoColumnRef, I: IntoIterator<Item = (T, Order, NullOrdering)>,

Order by vector of columns with nulls order option.

impl PartialEq for UpdateStatement

fn eq(&self, other: &UpdateStatement) -> bool

Tests for self and other values to be equal, and is used by ==.

fn ne(&self, other: &Rhs) -> bool

Tests for !=. The default implementation is almost always sufficient, and should not be overridden without very good reason.

impl QueryStatementBuilder for UpdateStatement

fn build_collect_any_into( &self, query_builder: &dyn QueryBuilder, sql: &mut dyn SqlWriter, )

Build corresponding SQL statement into the SqlWriter for certain database backend and collect query parameters

fn build_any(&self, query_builder: &dyn QueryBuilder) -> (String, Values)

Build corresponding SQL statement for certain database backend and collect query parameters into a vector

fn build_collect_any( &self, query_builder: &dyn QueryBuilder, sql: &mut dyn SqlWriter, ) -> String

Build corresponding SQL statement for certain database backend and collect query parameters

fn into_sub_query_statement(self) -> SubQueryStatement

impl QueryStatementWriter for UpdateStatement

fn build_collect_into<T: QueryBuilder>( &self, query_builder: T, sql: &mut dyn SqlWriter, )

fn to_string<T: QueryBuilder>(&self, query_builder: T) -> String

Build corresponding SQL statement for certain database backend and return SQL string

fn build<T: QueryBuilder>(&self, query_builder: T) -> (String, Values)

Build corresponding SQL statement for certain database backend and collect query parameters into a vector

fn build_collect<T: QueryBuilder>( &self, query_builder: T, sql: &mut dyn SqlWriter, ) -> String

Build corresponding SQL statement for certain database backend and collect query parameters

impl StructuralPartialEq for UpdateStatement