UPDATE SET values from list

Question

I have a problem updating a table with new values from a list.

My data looks like this:

id  value_1  value_2

1   11       21

2   32       41

3   43       84

...

I already wrote the column id and value_1 with an INSERT command in the table. At that step, I cannot write the value_2 column as I still need to calculate it, so I want to update the table later with an array of values of the value_2 column.

I would like to have a code something like this:

UPDATE table_name SET value_2 = (21,41,84) WHERE id IN (1,2,3)

Unfortunately, it's not possible like this to SET value_2 from a list, it works only with single values.

I got a workaround with writing a for loop over the whole UPDATE query, but this was too slow for my program. Anyone has a suggestion how I could get this working?

The whole query is performed with Python.

This is how the `UPDATE` statement works. It can modify more than one row but it sets the field `value_2` of all the rows to the same new value. The feature you described does not exist in SQL. You have to issue 3 different `UPDATE` queries to change the field `value_2` of three rows to three different values. — axiac, May 18 '17 at 09:36
@InnovaITveSolutions please note that you don't need to add tag information to the title. — Bugs, May 18 '17 at 09:41
I think you can query a list of data and create a dynamic query using group_concat(), then execute them. — Hassan Mudassir, Sep 27 '19 at 09:49

score 2 · Answer 1 · answered May 18 '17 at 09:40

2

It can be done with one single UPDATE, using a case expression to set the wanted value. Something like this:

UPDATE table_name
SET value_2 = case id when 1 then 21
                      when 2 then 41
                      when 3 then 84
              end
WHERE id IN (1,2,3)

However, I don't know if it will make any performance difference.

answered May 18 '17 at 09:40

jarlh

42,561
8
45
63

Thank you, this answer is useful at least for a small data size, however my data has around 10000 values in each column, so I cannot make a case for each one. Is it possible to implement lists in the case expression? – Philipp May 18 '17 at 09:54
@Philipp do multiple updates in transaction just like all people do. you can make temporary table, insert new data there, then do one update but it's bad idea – Peter May 18 '17 at 10:44
But `BEGIN`...`COMMIT` around the 10K updates; that will speed it up some. – Rick James May 19 '17 at 18:29
If we are hardcoding values in the CASE then why we need to go for this, we can use those much update query. – Hassan Mudassir Sep 27 '19 at 09:47

score 2 · Accepted Answer · edited May 23 '17 at 12:26

After trying different things I could find a solution to update values of table fast using value lists as input.

I orientated on the idea presented in this question (https://stackoverflow.com/a/3466/7997169) and modified it to deal with lists.

As input I have lists like this:

id = [1, 2, 3, ... ]
value_1 = [11, 32, 41, ... ]
value_2 = [21, 41, 84, ... ]

Using the Python MySQL connector I could write the whole update loop into one query command. Therefore I wrote the data from the lists into a string looking like this:

VALUES (1,11,21),(2,32,41),(3,41,84),....

The total code looks like this:

for i in range(0,len(id),1):
    a = '(' + str(id[i]) + ',' + str(value_1[i]) + ',' + str(value_2[i]) + ')'
    b = ','
    if i < (len(id)-1):
        c = c + a + b
    else:
        c += a

update_cell_info = ("INSERT INTO table (id, value_1, value_2)"
                      "VALUES %s" % c +
                      "ON DUPLICATE KEY UPDATE "
                      "value_1=VALUES(value_1),"
                      "value_2=VALUES(value_2)"
                      ";")
cursor.execute(update_cell_info)

In the end this procedure is over 10x faster than the previous one where I used a for loop to iterate the update process with new variables.

UPDATE SET values from list

2 Answers2