Bulk Update multiple rows in same query using PostgreSQL

I think you want:

update mytable
set 
    column_a = case when id = 1 then 'FINISH' else 'UNFINISHED' end,
    column_b = case when id = 1 then 1234     else 3124 end
where id in (1, 2)

Rationale:

• the where clause filters only on the ids you want to update, which is more efficient (provided you have an index on id ), and simplifies the conditional logic

• id looks like a number, so it should be treated as such (ie, do not surround the literal values with quotes)

• literal strings must be surrounded with single quotes - double quotes stand for identifiers (such as column names)

I am a fan of structuring this using a derived table:

UPDATE t
    SET column_a = v.column_a,
        column_b = v.column_b
    FROM (VALUES (1, 'FINISH', 1234),
                 (2, 'UNFINISH', 3124)
         ) v(id, column_a, column_b)
    WHERE v.id = t.id;

This makes it simple to add additional values -- both ids and columns -- without complicating the query. It is less prone to accidental errors as well.

The most efficient way to do this in Postgres is using UNNEST. This lets you pass one parameter per column (rather than number of rows x number of columns). For your example, this would look like:

UPDATE table
SET
  column_a=bulk_query.updated_column_a,
  column_b=bulk_query.updated_column_b
FROM
  (
    SELECT * FROM UNNEST(
      ?::TEXT[],
      ?::TEXT[],
      ?::INT[]
    ) AS t(id, updated_column_a, updated_column_b)
  ) AS bulk_query
WHERE
  users.id=bulk_query.id

Then you can pass three parameters:

[
  ["1", "2"],
  ["FINISH", "UNFINISH"],
  [1234, 3124]
]

The great thing about this approach, is that you only need those 3 parameters no matter how many rows you want to update.

I've written more about this topic in this blog post: https://www.atdatabases.org/blog/2022/01/21/optimizing-postgres-using-unnest#update-multiple-records-to-different-values-in-a-single-query

You can use CASE expression.

UPDATE table 
SET 
  column_a = CASE WHEN id = '1' THEN 'FINISH'
                  WHEN id = '2' THEN 'UNFINISH'
                  ELSE 'SOMETHING'
             END,
  column_b = CASE WHEN id = '1' THEN 1234
                  WHEN id = '2' THEN 3124
                  ELSE 0
             END;

In postgresql you can use the update-from-values approach described in the answers here: Update multiple rows in same query using PostgreSQL

And in Go you can implement that like this:

func updateWithSlice(slice []T) error {
    var queryString = `UPDATE "table" AS t SET (
        "column_a"
        , "column_b"
    ) = (
        x."column_a"::text
        , x."column_b"::integer
    )
    FROM (VALUES` // `

    numColumns := 3 // the number of columns you want to update + 1 for the id column
    params := make([]interface{}, len(slice)*numColumns)
    for i, t := range slice {
        pos := i * numColumns

        params[pos+0] = t.ColumnA
        params[pos+1] = t.ColumnB
        params[pos+2] = t.Id

        queryString += `($` + strconv.Itoa(pos+1) +
            `,$` + strconv.Itoa(pos+2) +
            `,$` + strconv.Itoa(pos+3) +
            `),`
    }

    queryString = queryString[:len(queryString)-1] // drop last ","
    queryString += ` ) AS x (
        "column_a"
        , "column_b"
        , "id"
    )
    WHERE t."id" = x."id"::integer` // `

    _, err := db.Exec(queryString, params...)
    return err
}