使用 PostgreSQL 批量更新同一查询中的多行 [英] Bulk Update multiple rows in same query using PostgreSQL

问题描述

我希望在一个语句中更新 PostgreSQL 和 Go 中的多行.有没有办法做如下的事情?

I'm looking to update multiple rows in PostgreSQL and Go in one statement. Is there a way to do something like the following?

UPDATE table 
SET column_a = "FINISH", 
    column_b = 1234 
WHERE id = '1',
    column_a = "UNFINISH", 
    column_b = 3124 
WHERE id = '2' 

如果用 go 语言执行，是否有示例?

and is there an example, if executed in go language?

推荐答案

在 postgresql 中，您可以使用此处答案中描述的 update-from-values 方法:使用PostgreSQL更新同一查询中的多行

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

在 Go 中你可以这样实现:

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
}

这篇关于使用 PostgreSQL 批量更新同一查询中的多行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！