Change more than one row in postgresql

Question

I need to change rows in my DB using 2 arrays, first stores names of rows which i need to change, second stores val. I've added the code , to understand what I want to do. Can i do it with 1 request to my DB

func update_1(){
    key := []string{"Name1", "Name2", "Name4"}
    val := []string{"1", "2", "4"}
    for i, _ := range key{
        _, err := db.Exec("UPDATE table SET val = $1 WHERE name = $2",val[i], key[i])
        if err != nil {
            errorLog.Println(err)
            return
        }
    }
}

It might be possible with [SQL's case statement](https://www.w3schools.com/sql/sql_case.asp), also please include your code as text — Cookie04, May 02 '21 at 17:07
I found this similar question about mysql, perhaps it answers your question: https://stackoverflow.com/questions/25674737/mysql-update-multiple-rows-with-different-values-in-one-query/25674827 — Cookie04, May 02 '21 at 17:13
you would need to generate the sql at runtime. Loop over each entry in your slice and add a case for it in your sql. — Cookie04, May 02 '21 at 17:14
you can use a buffer and write to it in a loop, then execute the resulting string with `db.Exec()` — Cookie04, May 02 '21 at 17:25

score 3 · Accepted Answer · answered May 02 '21 at 17:41

3

You can pass the arrays into a Postgres query as parameters. Then it is a simple unnest() and update:

update t
    set val = u.val
    from unnest(:ar_names, :ar_vals) u(name, val)
    where t.name = u.name;

answered May 02 '21 at 17:41

Gordon Linoff

1,242,037
58
646
786

1

db.Exec("update test set val = u.val from unnest($1::bigint[], $2::character varying[]) u(name, val) where test.name = u.name", pq.Array([]int{1, 3}), pq.Array([]string{"228", "228"})) my final request in golang – kokolen May 02 '21 at 18:39

Change more than one row in postgresql

1 Answers1