On this post I’m going to be covering a neat trick I learned to dynamically update multiple rows in a table with a single query. For this example I have a table called test_table and the structure looks like this:

For this post I used python for the application login and pscyopg2 as the postgreSQL client. The entire script is below.

import psycopg2
import psycopg2.extras

#connect to DB
conn = psycopg2.connect("dbname='db' user='user' host='localhost' password='foobar'")
cur = conn.cursor(cursor_factory=psycopg2.extras.DictCursor)

#list of rows to update
updates = [{"id" : 1, "col1" : "row1col1_edited", "col2" : "row1col1_edited"},
{"id" : 2, "col1" : "row2col1_edited", "col2" : "row2col2_edited"}]

#create query string
qStr = "UPDATE test_table SET col1 = col1_new, col2 = col2_new FROM (VALUES "
qParams = []
for r in updates:

    qStr += "(%s, %s, %s),"
    qParams.extend([r["id"],r["col1"],r["col2"]])
qStr = qStr[:-1]
qStr += " ) AS tmp(id, col1_new, col2_new) WHERE tmp.id = test_table.id"

#execute query
cur.execute(qStr,qParams)
conn.commit()
cur.close()

At the end of the script the qStr variable contains the entire query string:
UPDATE test_table SET col1 = col1_new, col2 = col2_new
FROM (VALUES (%s, %s, %s),(%s, %s, %s) )
AS tmp(id, col1_new, col2_new)
WHERE tmp.id = test_table.id

and the qParams variable contains:
[1, 'row1col1_edited', 'row1col1_edited', 2, 'row2col1_edited', 'row2col2_edited']

Hope you enjoy! Until next time.