Several ways to update batches in MySQL

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

You may also be interested in:
  • MySQL batch update and batch update of different values ​​of multiple records
  • Summary of Mysql update multi-table joint update method
  • Batch modification, clearing, copying and other update commands for MySQL data table field contents
  • MySQL implements batch update of data in different tables

<<:  Axios project with 77.9K GitHub repository: What are the things worth learning?

>>:  How to check PCIe version and speed in Linux

Recommend

Detailed graphic tutorial on installing Ubuntu 20.04 dual system on Windows 10

win10 + Ubuntu 20.04 LTS dual system installation...

Solution to the ineffective margin of div nested in HTML

Here's a solution to the problem where margin...

Solution to the docker command exception "permission denied"

In Linux system, newly install docker and enter t...

Solve the problem of inconsistent front and back end ports of Vue

Vue front and back end ports are inconsistent In ...

Solution to the blank page after vue.js packaged project

I believe that many partners who have just come i...

Simple encapsulation of axios and example code for use

Preface Recently, when I was building a project, ...

What is the base tag and what does it do?

The <base> tag specifies the default addres...

jQuery canvas generates a poster with a QR code

This article shares the specific code for using j...

In-depth explanation of InnoDB locks in MySQL technology

Table of contents Preface 1. What is a lock? 2. L...

In-depth understanding of MySQL master-slave replication thread state transition

Preface The basic principle of MySQL master-slave...

A brief discussion on the specific use of viewport in mobile terminals

Table of contents 1. Basic Concepts 1.1 Two kinds...

How to install Docker on Windows 10 Home Edition

I recently used Docker to upgrade a project. I ha...

Solution to the problem that Docker container cannot be stopped or killed

Docker version 1.13.1 Problem Process A MySQL con...

Implementation of tomcat deployment project and integration with IDEA

Table of contents 3 ways to deploy projects with ...