CODEWITHSUNDEEP
mysqlsqlmysql-python
Philipp
Philipp

Reputation: 61

UPDATE SET values from list

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.

Upvotes: 4

Views: 20758

Answers (2)

Philipp
Philipp

Reputation: 61

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.

Upvotes: 2

jarlh
jarlh

Reputation: 44795

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.

Upvotes: 5

Related Questions