CODEWITHSUNDEEP
pythonsqldatabase
CullenStone
CullenStone

Reputation: 1

Parameterizing ON DUPLICATE KEY UPDATE

So I have a SQL query ran in python that will add data to a database, but I am wondering if there is a duplicate key that just updates a couple of fields. The data that I am using is around 30 columns, and wondering if there is a way to do this.

data = [3, "hello", "this", "is", "random", "data",.......,44] #this being 30 items long
car_placeholder = ",".join(['%s'] * len(data))
qry = f"INSERT INTO car_sales_example VALUES ({car_placeholder}) ON DUPLICATE KEY UPDATE 
                         Price = {data[15]}, IdNum = {data[29]}"
cursor.execute(qry, data)
conn.commit()

I want to be able to add an entry if the key doesn't exist, but if it does, update some of the columns within the entry which is that being the Price and the IdNum, which are at odd locations in the dataset. Is this even possible?

If this is not, is there a way to update every column within the database without explicitly saying it. For example

qry = f"INSERT INTO car_sales_example VALUES ({car_placeholder}) ON DUPLICATE KEY UPDATE 
                        car_sales_example VALUES ({car_placeholder})"

instead of going column by column -> 

ON DUPLICATE KEY UPDATE Id = %s, Name = %s, Number = %s, etc... #for 30 columns

Upvotes: 0

Views: 28

Answers (1)

Barmar
Barmar

Reputation: 780871

In ON DUPLICATE KEY UPDATE you can use the VALUES() function with the name of a column to get the value that would have been inserted into that column.

ON DUPLICATE KEY UPDATE price = VALUES(price), idnum = VALUES(idnum)

Upvotes: 1

Related Questions