Should primary key columns be added in the UPDATE?

January 3, 2023

In the example code below, col1 and col2 are primary keys in the database!

My question is: should they be added in the part of the code after the ON DUPLICATE KEY UPDATE, as it is already in the code, or should they not be added?

Example code:

with Dl.cursor() as cursor:
    for chunk in np.array_split(DataFrame, 10, axis=0):
        for i in chunk.index:
            cursor.execute("INSERT INTO table_example (col1, col2, col3, col4) VALUES (%s, %s, %s, %s) ON DUPLICATE KEY UPDATE col1 = col1, col2 = col2, col3 = col3, col4 = col4;", (chunk['col1'][i], chunk['col2'][i], chunk['col3'][i], chunk['col4'][i]))
                                                                                                                         # col3 = col3, col4 = col4; ... Which version is correct?
            Dl.commit()
    cursor.close()
Dl.close()

>Solution :

It is not necessary to update the unique key or primary so your SQL can be like this:

INSERT INTO table_example (col1, col2, col3, col4)
VALUES ('col1', 'col2', 'col3', 'col4')
ON DUPLICATE KEY UPDATE col3 = :col3, col4 = :col4

The SQL already knows that it is in the context of col1 and col2 as the duplicate key.

So for your code it should be something like:

with Dl.cursor() as cursor:
for chunk in np.array_split(DataFrame, 10, axis=0):
    for i in chunk.index:
        cursor.execute("""
            INSERT INTO table_example (col1, col2, col3, col4) 
            VALUES (%s, %s, %s, %s) 
            ON DUPLICATE KEY UPDATE col3 = %s, col4 = %s;""", 
            (chunk['col1'][i], chunk['col2'][i], chunk['col3'][I], 
            chunk['col4'][i]), chunk['col3'][i], chunk['col4'][i]))
    Dl.commit()
cursor.close()