AdonisJS Postgres - Multiple Row Update with Different Values in a Single Query

adonisjs_postgres_multiple_row_update_with_different_values_in_a_single_query.js

taken from: https://forum.adonisjs.com/t/bulk-update-lucid-model/2505/2

  const placeholders = []
  const values = []
  for (const pickup of pickups) {
    placeholders.push('(?,?)')
    values.push(pickup.id)
    values.push(pickup.city)
  }

  // Bulk update with individual pickup cities
  await Database.raw(`UPDATE pickups AS p SET city = v.city
                      FROM (VALUES ${placeholders.join(',')})
                      AS v(id, city) WHERE p.id = v.id`, values)

  // You may need to typecast values in the Query, like:
  // v.city will become v.city::int4 or v.city::text
  // same goes for v.id
  // otherwise you may get error in the latest versions of Postgres