case_update.py

import mysql.connector
import time

# Connect to MySQL database
conn = mysql.connector.connect(
    host="127.0.0.1",
    user="root",
    port="4000",
    database="test"
)
cursor = conn.cursor()

def prepare_test_data(cursor, conn):
    # Create the institutions table
    cursor.execute("DROP TABLE IF EXISTS institutions")
    cursor.execute("""
        CREATE TABLE institutions (
            id INT PRIMARY KEY,
            data VARCHAR(255)
        )
    """)

    # Insert 40,000 rows into the table
    insert_data = [(i, f"original_data_{i}") for i in range(1, 40001)]
    cursor.executemany("INSERT INTO institutions (id, data) VALUES (%s, %s)", insert_data)
    conn.commit()

# Uncomment the following line to re-create and reload the table
prepare_test_data(cursor, conn)

# Prepare values for the UPDATE
update_pairs = [(i, f"updated_data_{i}") for i in range(1, 40001)]

# Construct the SQL UPDATE statement
update_sql = "UPDATE institutions SET data = CASE " + \
    " ".join(f"WHEN id={i} THEN '{val}'" for i, val in update_pairs) + \
    " ELSE data END"

# Measure execution time
start_time = time.time()
cursor.execute(update_sql)
conn.commit()
end_time = time.time()

print(f"Update completed in {end_time - start_time:.2f} seconds.")

# Clean up
cursor.close()
conn.close()