Venkatesh Blog
MySQL Multiple rows update in Single Query

MySQL Multiple rows update in Single Query

Venkatesh Chary's photo
Venkatesh Chary
·

2 min read

We will discuss based on the use cases

Why do we need to write in a single query?

I am using the AWS lambda function. it will trigger on the s3 event, whenever a new file is inserted into the s3 file system our lambda function will be called, here I am doing the insert or update

if we have less data it will be easier to process updates

limitation in lambda or forced me to do updates in a single query

  1. lambda have time out for 15 minutes (more than that it won't run)
  2. if the data is more insert or update the queries will take more time to run

Query Skelton

update {table_name} set {col1} = ( CASE id {query} END ), {col2} = (CASE id {query2} END ) where id in{tid};

MySQL connection

import pymysql

def connect_to_db():
    try:
        return pymysql.connect(host=config.host,
                               port=3306,
                               user=config.username,
                               passwd=config.password,
                               db=config.database, autocommit=True)
    except Exception as e:
        print('Got error {!r}, errno is {}'.format(e, e.args[0]))

def insert_update():
       conn = connect_to_db()
       cur = conn.cursor(pymysql.cursors.DictCursor)
       try: 
             pass
       except Exception as e:
            print("exception:",e)
       finally:
            cur.close()
            conn.close()

Dynamic query creation

col1 = "name"
query = ""
when_condition = "when {id} then '{value}' "
query = query + " " + when_condition.format(id=existing["id"],
                                                                                   value="venky")
# final query after concatination

query = when 1 then 'venky' when 2 then 'victory'

# for query2

col2 = "amount"
query2 = ""
when_condition = "when {id} then '{value}' "
query2 = query2 + " " + when_condition.format(id=existing["id"],
                                                                                   value="1000")

# final query after concatenation
query = when 1 then 1000 when 2 then 1200

# tid is all pk id's in the format of tuple if we have multiple 
eg: tid= (1,2)

udate_stmnt = update {table_name} set {col1} = ( CASE id {query} END ), {col2} = (CASE id {query2} END ) where id in{tid};

update_stmnt.format(table_name="user", col1=col1, query= query, col2=col2, query2= query2, tid=tid)

Exection time taken

  • for a single query update it took nearly 0.2 sec, I have 2 lakh data updates the case is the worst
  • using the above single it ran in 2 minutes

Thanks (.~.)

PythonPython 3MySQL