You signed in with another tab or window. Reload to refresh your session.You signed out in another tab or window. Reload to refresh your session.You switched accounts on another tab or window. Reload to refresh your session.Dismiss alert
INSERT INTO`test`.`t_json_test` (
`id`,
`age`,
`username`
)
VALUES (?, ?)
ON DUPLICATE KEY UPDATE`age`=VALUES(`age`),
`username`= IF(VALUES(`username`) IS NOT NULL,
JSON_MERGE_PATCH(
IF(`t_json_test`.`username`IS NOT NULL,
`t_json_test`.`username`,
JSON_OBJECT()),
VALUES(`username`)),
`t_json_test`.`username`)
how to translate it into Sea-Query update conflict?
I have tried like this:
letmut data = Map::new();
data.insert("abcd".into(), serde_json::Value::String("1111".to_string()));let m = model::t_json_test::ActiveModel{id:ActiveValue::Set(2),age:ActiveValue::Set(Some(22)),username:ActiveValue::Set(Some(Json::Object(data))),};let insert = t_json_test::Entity::insert(m).on_conflict(
sea_query::OnConflict::column(t_json_test::Column::Id).update_column(t_json_test::Column::Age).value(t_json_test::Column::Username,Expr::cust_with_exprs("IF(? IS NOT NULL, ?, ?)",[Expr::cust_with_expr("VALUES(?)",Expr::col(t_json_test::Column::Username)).into(),Expr::cust_with_exprs("JSON_MERGE_PATCH(IF(? IS NOT NULL, ?, JSON_OBJECT()), ?)",[Expr::col((t_json_test::Table::Name, t_json_test::Column::Username)).into(),Expr::col((t_json_test::Table::Name, t_json_test::Column::Username)).into(),Expr::cust_with_expr("VALUES(?)",Expr::col(t_json_test::Column::Username)).into(),],),Expr::col((t_json_test::Table::Name, t_json_test::Column::Username)).into()])).to_owned());// Output SQL: "INSERT INTO `t_json_test` (`id`, `age`, `username`) VALUES (2, 22, '{\\\"abcd\\\":\\\"1111\\\"}') ON DUPLICATE KEY UPDATE `age` = VALUES(`age`), `username` = IF(VALUES(`username`) IS NOT NULL, JSON_MERGE_PATCH(IF(`t_json_test`.`username` IS NOT NULL, `t_json_test`.`username`, JSON_OBJECT()), VALUES(`username`)), `t_json_test`.`username`)"println!("SQL: {:?}", insert.build(DatabaseBackend::MySql).to_string());
a slight deficiency is no VALUES(col) in Expr, and MySQL note me that VALUES function is deprecated and will be removed in a future release. Please use an alias (INSERT INTO ... VALUES (...) AS alias) and replace VALUES(col) in the ON DUPLICATE KEY UPDATE clause with alias.col instead.
reacted with thumbs up emoji reacted with thumbs down emoji reacted with laugh emoji reacted with hooray emoji reacted with confused emoji reacted with heart emoji reacted with rocket emoji reacted with eyes emoji
-
Hello everyone, I have a MySQL upsert SQL like:
how to translate it into Sea-Query update conflict?
I have tried like this:
a slight deficiency is no
VALUES(col)
inExpr
, and MySQL note me thatVALUES
function is deprecated and will be removed in a future release. Please use an alias (INSERT INTO ... VALUES (...) AS alias) and replace VALUES(col) in the ON DUPLICATE KEY UPDATE clause with alias.col instead.Beta Was this translation helpful? Give feedback.
All reactions