MySQL ON DUPLICATE KEY UPDATE with all variables

Question

I have below MySQL insert statement which I want to update all the fields(except for the primary key) if a record exists. Because there are up to 80 columns, I don't want to write the key and value pairs one by one. Could anyone help on this?

Below is my code:

 # Save item to database
def process_item(self, item, _):
    with self.conn.cursor() as c:
        c.execute(self.query(item), item.values())
def query(self, item):
    return "INSERT INTO income_statement ({columns}) VALUES ({values}) ON DUPLICATE KEY UPDATE ({columns}) = VALUES({columns})".format(
        columns=', '.join(item.keys()),
        values=', '.join(['%s'] * len(item))
    )

I got this error:

ProgrammingError: (1064, u"You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(unit_name, fd_stock_dividend, fd_administration_fee, fd_depreciation, fd_divide' at line 1")

Possible duplicate of [Is there a way to use ON DUPLICATE KEY to Update all that I wanted to insert?](https://stackoverflow.com/questions/9537710/is-there-a-way-to-use-on-duplicate-key-to-update-all-that-i-wanted-to-insert) — Paul Spiegel, Jun 23 '18 at 10:16
The possible duplicate answer did not solve my problem as I want to write the update statement without listing all the columns — Nicholas Kan, Jun 23 '18 at 10:49
The question is quite the same. The answer: You can't do it that way. Either update every column, or use `REPLACE` instead of `INSERT` (see 2nd answer). — Paul Spiegel, Jun 23 '18 at 10:59

J_Scholz · Answer 1 · 2022-09-19T10:30:39.380

You need to prepare the second part of the statement. It's not entirely clear, what your items look like but soemthing like this:

def query(self, item):
    col = item.keys()
    val = item.values()
    update_statement = ', '.join([f'{c} = {v}' for c,v in zip(col,val)])
    return "INSERT INTO income_statement ({columns}) VALUES (%({values})s) ON DUPLICATE KEY UPDATE ({update})".format(
        columns=', '.join(col),
        values=')s, %('.join(val),
        update=update_statement
    )

Alternatively depending on your mysql version and what your data looks like, the following string-joins could be the right:

update_statement = ', '.join([f'{c} = VALUES({c})' for c in col)])

Or:

update_statement = ', '.join([f'{c} = new.{c}' for c in col)])
return "INSERT INTO income_statement ({columns}) VALUES (%({values})s) AS NEW ON DUPLICATE KEY UPDATE ({update})".format(
            columns=', '.join(col),
            values=')s, %('.join(val),
            update=update_statement
        )

See this answer for referral: MySQL ON DUPLICATE KEY UPDATE for multiple rows insert in single query

MySQL ON DUPLICATE KEY UPDATE with all variables

1 Answers1

Linked