使用 PostgreSQL 批量更新同一查询中的多行

Question

我希望在一个语句中更新 PostgreSQL 和 Go 中的多行。有没有办法做类似下面的事情？UPDATE table SET column_a = "FINISH",     column_b = 1234 WHERE id = '1',    column_a = "UNFINISH",     column_b = 3124 WHERE id = '2' 如果用 go 语言执行，是否有示例？

Answer 1

我喜欢使用派生表来构建它：

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;

这使得添加附加值（ids 和列）变得简单，而不会使查询复杂化。它也不太容易出现意外错误。

Answer 2

在 postgresql 中，您可以使用此处答案中描述的 update-from-values 方法：Update multiple rows in same query using PostgreSQL

在 Go 中你可以这样实现：

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

}

Answer 3

在 Postgres 中最有效的方法是使用UNNEST. 这使您可以每列传递一个参数（而不是行数 x 列数）。对于您的示例，这看起来像：

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

然后可以传递三个参数：

[

  ["1", "2"],

  ["FINISH", "UNFINISH"],

  [1234, 3124]

]

这种方法的好处在于，无论您要更新多少行，都只需要这 3 个参数。