K
KyselyPogPEGA

Conditionally updating fields

How would i go about doing something like UPDATE table SET field = IF(condition, trueValue, falseValue) WHERE someCondition;, i can't exactly seem to find that in the docs Driver used: MySQL
PogPEGA
PogPEGA•19d ago
actually, this can be done with <ExpressionBuilder>.case(), but is it possible to write smth like id = id - 1 or would i be sticking to raw sql for scenarios like these?
Solution
Igal
Igal•19d ago
Hey 👋 Something like: 
await db
  .updateTable("person")
  .set("children", (eb) =>
    eb
      .case()
      .when("has_spouse", "=", eb.lit(1))
      .then(eb("children", "+", eb.lit(1)))
      .else(eb.ref("children"))
      .end(),
  )
  .where("first_name", "=", "Bob")
  .execute();
await db
  .updateTable("person")
  .set("children", (eb) =>
    eb
      .case()
      .when("has_spouse", "=", eb.lit(1))
      .then(eb("children", "+", eb.lit(1)))
      .else(eb.ref("children"))
      .end(),
  )
  .where("first_name", "=", "Bob")
  .execute();
https://kyse.link/mqB8C eb.lit is optional here, you could just pass the 1s and have them parametrized. eb(...) allows creating all sorts of binary operations, where by default, the LHS is a column reference, and RHS is a value.
Want results from more Discord servers?
Add your server
More Posts
How to cast to date. eg : `DATE(created_at)`How to write this query in kysely ```sql SELECT DATE(created_at) AS date, SUM(points) AS score FROtransaction takes too longI have a problem with transactions. What might be the reason that db.transaction() adds more than 10Syntax error when empty array is passed to a WHERE filter (request for more readable errors)In a query like `someQuery.where("x", "in", xs)`, if `xs` is empty, then the error will be somethingDerived tables for PostgresHi, I am trying to do a bulk update like this: ``` UPDATE your_table SET someColumn = new_values.newComplex Query Builder from dynamic input - Typescript issueHi, it's me - again 🙂 I'm currently trying to migrate our current query builder. It seems that eHow to add table/column comments when creating tables?Hey, we're approaching close to 100 tables in our project. New people will be joining our founding tNeed help improving a custom helperMy dopamine with Kysely is quite high right now, so I am experimenting all sorts of things to createjsonArrayFrom with `as` not being typedI have the following query ```ts await db .selectFrom('user') .selectAll() .select((eb)