MySQL Error with Insert/Update on Duplicate Key

Question

Whats wrong with the following statement?

I'm trying to insert data into my table but update it if there is a duplicate entry - When I run it, I get the following error:

(1064, "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 'updat e temp set impressions = impressions + 1, clicks = clicks + 0, ctr = (ctr +' at line 1")

def insert_or_update_new(self, profile_id, landing_page, keyword, position, impressions, clicks, ctr):
    try:
        self.cursor.execute('''insert into temp (profile_id, landing_page, keyword, position, impressions, clicks, ctr) values (%s, %s, %s, %s, %s, %s, %s) on duplicate key update temp set impressions = impressions + %s, clicks = clicks + %s, ctr = (ctr + %s / 2)''', (profile_id, landing_page, keyword, position, impressions, clicks, ctr, impressions, clicks, ctr))
        self.db.commit()
    except Exception as e:
        self.db.rollback()
        # Rollback in case there is any error
        return e

Update: Removed duplicate UPDATE - error still there.

You've duplicated update update.

cwallenpoole
– cwallenpoole

2016-12-12 21:44:04 +00:00
Commented Dec 12, 2016 at 21:44 — cwallenpoole
– cwallenpoole, Commented Dec 12, 2016 at 21:44

rafwlaz · Accepted Answer · 2016-12-12 21:48:24Z

1

Your statement should look not like

on duplicate key update update temp set impressions...

but

on duplicate key update impressions=...

answered Dec 12, 2016 at 21:48

rafwlaz

4842 silver badges17 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Adders Over a year ago

You beauty :) Will accept once it allows me to. Any explanation on why this is?

rafwlaz Over a year ago

look at link provided by larwa1n below and there is a syntax you can use :)

Iarwa1n · Accepted Answer · 2016-12-12 21:44:12Z

0

Your statement includes the word update twice, removing one might do the trick :-)

answered Dec 12, 2016 at 21:44

Iarwa1n

4603 silver badges12 bronze badges

2 Comments

Adders Over a year ago

Well spotted, however still getting (1064, "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 'set i mpressions = impressions + 1, clicks = clicks + 0, ctr = (ctr + 0 / 2)' at line 1")

Iarwa1n Over a year ago

I think the set is also not needed. See dev.mysql.com/doc/refman/5.7/en/insert-on-duplicate.html

Collectives™ on Stack Overflow

MySQL Error with Insert/Update on Duplicate Key

2 Answers 2

2 Comments

2 Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

2 Comments

Related