Updating multiple columns with different values for multiple rows in one query

user_15443550 0
Viewed 284

I'm working with mysql and golang. Let's say I have a table with the following columns: ID, value1

Given an array of IDs that I want to update, as well as a an array of value1, where ID[i] corresponds to the value of value1[i]. Is it possible for me to use the array and update the multiple rows in one query?

I've seen the case-when-then query, which would require quite a bit of formatting, hence I was wondering if there is a simpler way to do it. Furthermore, is there a way to extend this if there a more columns.

For example, given an array of ID = [1, 2, 3], val1 = [2, 4, 5], val2 = [3, 5, 6], we updated row with ID = 1, to be val1 = 2, val2 = 3 and so on. In this "multiple columns" case, the case-when-then query would be overly complicate since one case works for one column. The resulting table would then look like this:

ID val1 val2
1 2 3
2 4 5
3 5 6

Would appreciate this!

go mysql
1 Answers

As MySQL does not have a natural array data type, we commonly store the values in a table instead. Supposing we have three tables,namely a_id(id int),a_val1(val1 int),a_val2(val2 int) each for a different array, we can use three cursors to fetch from each and update the result table t (id int,val1 int,val2 int,row_id int primary key auto_increment) with the values through a loop. As why we need an additional row_id PK AI column in the table, that's to avoid gratuitous UPDATE, which I shall explain at the end.

delimiter //
drop procedure if exists insert_from_array//
create procedure insert_from_array()
begin
declare c_row_id int;
declare c_id int;
declare c_val1 int;
declare c_val2 int;
declare fin bool default false;
declare c0 cursor for select row_id from t;
declare c1 cursor for select id from a_id;
declare c2 cursor for select val1 from a_val1;
declare c3 cursor for select val2 from a_val2;
declare continue handler for not found set fin=true;
open c0;
open c1;
open c2;
open c3;

lp:loop
    fetch  c0 into c_row_id;
    fetch  c1 into c_id;
    fetch  c2 into c_val1;
    fetch c3 into c_val2;
    if fin=true then
        leave lp;
    end if;

    update t set id=c_id,val1=c_val1,val2=c_val2 where row_id=c_row_id;

end loop lp;

end//
call insert_from_array//

As you can see , we use a row_id in the WHERE clause to get the intended row to update rather than use id. If we used id, there was a possibility we might update the same row twice. For instance, the original table's id column has three values 1,2,3 . We would like to change them to 3,4,5 respectively. However, should the id be used in the WHERE clause to define the row to update. The first row would be updated twice(the first time changed to 3 during the update of row1,then it would be changed to 5 during the update of row3) Therefore, using a row_id to define the correct row is strongly recommended. Of course, if you are using MySQL 8.0, the window function row_number can do the same job.
And if you ABSOLUTELY have to use one single UPDATE statement to do everything. Add a row_id column for each of the 3 array tables and the main table. Then try using correlated subqueries in the UPDATE:

update t set id=(select id from a_id where a_id_rowid=t.row_id),
val1=(select val1 from a_val1 where a_val1_rowid=t.row_id),
val2=(select val1 from a_val1 where a_val1_rowid=t.row_id)
;
user_185543690
Related