Several ways to update batches in MySQL

Typically, we use the following SQL statement to update field values:

UPDATE mytable SET myfield='value' WHERE other_field='other_value';

But what would you do if you want to update multiple rows of data and the field values ​​in each row are different? At first you might think of using a loop to execute multiple UPDATE statements, like the following Python program example:

for x in xrange(10):
  sql = ''' UPDATE mytable SET myfield='value' WHERE other_field='other_value'; '''

There is nothing wrong with this method, and the code is simple and easy to understand, but more than one SQL query is executed in the loop statement. When optimizing the system, we always want to reduce the number of database queries as much as possible to reduce resource usage and improve system speed. Fortunately, there is a better solution. The SQL statement is a little more complicated, but you only need to execute one query. The syntax is as follows:

UPDATE mytable
  SET myfield = CASE other_field
    WHEN 1 THEN 'value'
    WHEN 2 THEN 'value'
    WHEN 3 THEN 'value'
  END
WHERE id IN (1,2,3)

This SQL statement is easy to understand. It uses the keyword CASE, which is common in many programming languages, to perform type judgments for different branches based on the value of the id field.

If you need to update multiple fields in a row, you can use the following SQL statement:

UPDATE categories
  SET display_order = CASE id
    WHEN 1 THEN 3
    WHEN 2 THEN 4
    WHEN 3 THEN 5
  END,
  title = CASE id
    WHEN 1 THEN 'New Title 1'
    WHEN 2 THEN 'New Title 2'
    WHEN 3 THEN 'New Title 3'
  END
WHERE id IN (1,2,3)

The above scheme greatly reduces the number of database query operations and greatly saves system resources.

However, this has a disadvantage: the issue that needs attention is the length of the SQL statement. It is necessary to consider the string length supported by the program running environment. Of course, this can also be expanded by updating the MySQL settings.

Of course, Python, such a powerful language, also provides us with such a powerful method as executemany, which can not only insert data, but also update data. As a person who often does things, these things need to be used frequently to compare the results.

update_sql = ''' UPDATE mayi_order_image 
set order_city = %s
where user_ip = %s and dt = %s and id = %s 
 and user_ip is not null and (order_city is null or order_city = '' )
 '''
pp = []
for x in xrange(len(result)):
  ip = result[x][0]
  id_ = result[x][1]
  add = dbip.lookup(str(ip))
  adds = add.split('\t')
  address = str(adds[0]) + ','+str(adds[1] )+ ','+ str(adds[2])
  pp.append((address,ip,end,id_))
  if x%5000 == 0:
    saveLog_many(update_sql,pp)
    pp = []
saveLog_many(update_sql,pp)

Is this more convenient? But as for the speed issue, I think it would be better to combine it with the second method for comparison.

Summarize

The above is the full content of this article. I hope that the content of this article will have certain reference learning value for your study or work. Thank you for your support of 123WORDPRESS.COM. If you want to learn more about this, please check out the following links