Bulk Update Multiple Rows in Same Query Using Postgresql

Update multiple rows in same query using PostgreSQL

You can also use update ... from syntax and use a mapping table. If you want to update more than one column, it's much more generalizable:

update test as t set
    column_a = c.column_a
from (values
    ('123', 1),
    ('345', 2)  
) as c(column_b, column_a) 
where c.column_b = t.column_b;

You can add as many columns as you like:

update test as t set
    column_a = c.column_a,
    column_c = c.column_c
from (values
    ('123', 1, '---'),
    ('345', 2, '+++')  
) as c(column_b, column_a, column_c) 
where c.column_b = t.column_b;

sql fiddle demo

Bulk Update multiple rows in same query using PostgreSQL

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
}

update multiple rows in one query but the input we are expecting is from json object of multiple data

You can do a bulk insert based on the json document. You should reformat the document as the format shown in the question is strange and unpractical.

Full working example:

create table example(id int primary key, email text, last_name text, first_name text);

with jsondata(jdata) as (
    values
    (
    '[
        {"id": 1, "email": "[xyz@abc.com]", "first_name": "John", "last_name": "Doe"},
        {"id": 2, "email": "[xyz@abc.com]", "first_name": "Robert", "last_name": "Duncan"},
        {"id": 3, "email": "[xyz@abc.com]", "first_name": "Ram", "last_name": "Das"},
        {"id": 4, "email": "[xyz@abc.com]", "first_name": "Albert", "last_name": "Pinto"},
        {"id": 5, "email": "[xyz@abc.com]", "first_name": "Robert", "last_name": "Peter"},
        {"id": 6, "email": "[xyz@abc.com]", "first_name": "Christian", "last_name": "Lint"},
        {"id": 7, "email": "[xyz@abc.com]", "first_name": "Mike", "last_name": "Hussey"},
        {"id": 8, "email": "[xyz@abc.com]", "first_name": "Ralph", "last_name": "Hunter"}
    ]'::jsonb)
)

insert into example 
select (elem->>'id')::int, elem->>'email', elem->>'last_name', elem->>'first_name'
from jsondata,
jsonb_array_elements(jdata) as elem;

The result:

select *
from example

 id |     email     | last_name | first_name 
----+---------------+-----------+------------
  1 | [xyz@abc.com] | Doe       | John
  2 | [xyz@abc.com] | Duncan    | Robert
  3 | [xyz@abc.com] | Das       | Ram
  4 | [xyz@abc.com] | Pinto     | Albert
  5 | [xyz@abc.com] | Peter     | Robert
  6 | [xyz@abc.com] | Lint      | Christian
  7 | [xyz@abc.com] | Hussey    | Mike
  8 | [xyz@abc.com] | Hunter    | Ralph
(8 rows)    

---

If you want to update the table (instead of insert into it):

with jsondata(jdata) as (
    -- values as above
)

update example set
    email = elem->>'email', 
    last_name = elem->>'last_name', 
    first_name = elem->>'first_name'
from jsondata,
jsonb_array_elements(jdata) as elem
where id = (elem->>'id')::int;

Update multiple rows at once (postgres)

First of all you must unnest your array:

WITH sample (id, name, status, age) AS (
    SELECT 
        * 
    FROM 
        --Using unnest function
        unnest(
            ARRAY[2323, 3434, 3434], 
            ARRAY['Andrey','Vasya','Petia'], 
            ARRAY[1,2,3], 
            ARRAY[23,45,54]
        )
)
--And then proceed to your update
UPDATE 
    users 
    SET status = s.status , name = s.name, age = s.age 
    FROM sample s
    WHERE users.id = s.id;

More info about unnest function here.

PostgreSQL: Update multiple rows in table with a single query

That's easily done with array operator any():

update car
set status = false
where id = any(array[1, 2, 3])

If id actually is of uuid datatype:

update car
set status = false
where id = any(array[...]::uuid[])

psycopg2: update multiple rows with one query

By splitting the list into chunks of size equal to page_size, it worked well:

def update_items(rows_to_update):
    sql_query = """UPDATE contact as t SET
                    name = data.name
                    FROM (VALUES %s) AS data (id, name)
                    WHERE t.id = data.id"""
    conn = get_db_connection()
    cur = conn.cursor()
    n = 100
    with tqdm(total=len(rows_to_update)) as pbar:
        for i in range(0, len(rows_to_update), n):
            psycopg2.extras.execute_values (
            cur, sql_query, rows_to_update[i:i + n], template=None, page_size=n
            )
            conn.commit()
            pbar.update(cur.rowcount)
    cur.close()
    conn.close()

Postgres UPDATE..FROM query with multiple updates on the same row

Example (assuming id is a PK in the target table, and {id, date_modified} is a PK in the source table)

UPDATE target dst
Set a = src.a , b = src.b
FROM source src
WHERE src.id = dst.id
AND NOT EXISTS (
        SELECT *
        FROM source nx
        WHERE nx.id = src.id
        -- use an extra key field AS tie-breaker
        AND nx.date_modified > src.date_modified
        );

---

(in fact, this is deduplication of the source table -> forcing the source table to the same PK as the target table)

---

Related Topics