Batch update support

[TmLev]

Does sqlx support batch update query? For example, like the one below:

UPDATE rows AS r
SET r.column1 = v.column1,
    r.column2 = v.column2
FROM (VALUES 
    (...pack of values from struct instance...),
    (...pack of values from struct instance...),
    ...
) AS v(id, column1, column2)
WHERE r.id = v.id

If so, how one should use it correctly?

[Swiftwork]

The syntax should simply be:

UPDATE rows AS r
SET r.column1 = v.column1,
    r.column2 = v.column2
FROM (VALUES
    (:id, :column1, :column2)
) AS v(id, column1, column2)
WHERE r.id = v.id

However, this doesn't currently work in v1.3.4 due to the current
regex requiring a closing bracket before values

sqlx/named.go

Line 227 in 92bfa36

var valuesReg = regexp.MustCompile(`\)\s*(?i)VALUES\s*\(`)

This was discussed by @QuangTung97 and @w1ck3dg0ph3r when it was initially implemented https://github.com/jmoiron/sqlx/pull/734/files#r632982543 and possible changes required to mitigate this.

Also worth noting is that update doesn't automatically coerce type https://www.postgresql.org/docs/14/sql-values.html and you might have to be explicitly cast the types. More info on this can be found in the comments on answer https://stackoverflow.com/a/18799497.

An example with a date: ... from (values ('2014-07-21'::timestamp, 1), ('2014-07-20', 2), ...

A final caveat is the casting using :: probably won't work with named queries #91, but a workaround could be CAST('2014-07-21' AS timestamp).

[chrichts]

For anyone else who gets here, another temp workaround until this is fixed is doing something like:

INSERT INTO rows (column1 ,column2 ) VALUES (:column1, :column2) ON DUPLICATE KEY UPDATE column1=:column1		

[dan-r95]

According to this comment , on a named batch update every variable after the VALUES part produces errors.