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'
and is there an example, if executed in go language?
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
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)
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
}
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;
column_a = 'SOMETHING' and 'coloumn_b = 0`. I do not think that is the intent.