Page 1 of 2 12 Last
  • Jump to page:
    #1
  1. Contributing User
    Devshed Beginner (1000 - 1499 posts)

    Join Date
    Jun 2003
    Location
    Thessaloniki
    Posts
    1,280
    Rep Power
    13

    Question ON DUPLICATE KEY UPDATE dilemma


    Hello, i'am trying to insert a new record or update an existing one in case counterID(stands for the page's URL) and cookieID(random number) is the same:
    Code:
        try:
            # if first time for webpage; create new record( primary key is automatic, hit is defaulted ), if page exists then update record
            cur.execute('''INSERT INTO counters (url) VALUES (%s) ON DUPLICATE KEY UPDATE hits = hits + 1''', page )
            # get the primary key value of the new added record
            cID = cur.lastrowid
           
            # if first time visitor on this page, create new record, if visitor exists then update record
            cur.execute('''INSERT INTO visitors (counterID, cookieID, host, city, useros, browser, ref, lastvisit) VALUES (%s, %s, %s, %s, %s, %s, %s, %s)
                           ON DUPLICATE KEY UPDATE cookieID = %s, host = %s, city = %s, useros = %s, browser = %s, ref = %s, hits = hits + 1, lastvisit = %s
                           WHERE counterID = %s and cookieID = %s''',
                           (cID, cookieID, host, city, useros, browser, ref, lastvisit, cookieID, host, city, useros, browser, ref, lastvisit, cID, cookieID) )
    =============

    Error is: ProgrammingError(ProgrammingError(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 'WHERE counterID = 1 and cookieID = '3815'' at line 3"),)

    i notticed that if i remove the WHERE clause in the last execute it works but then its not updating properly.

    Can this happen in 1-statemnt with the ON DUPLICATE KEY INVOLVED WITHOUT BREAKING IT IN IN 2-STATEMNTS?


    Actually what i want is this effect in cur.execute statement:
    Code:
            # if first time visitor on this page, create new record, if visitor exists then update record
            cur.execute('''INSERT INTO visitors (counterID, cookieID, host, city, useros, browser, ref, lastvisit) VALUES (%s, %s, %s, %s, %s, %s, %s, %s)''',
                           (cID, cookieID, host, city, useros, browser, ref, lastvisit)
                       
            cur.execute('''UPDATE visitors SET cookieID = %s, host = %s, city = %s, useros = %s, browser = %s, ref = %s, hits = hits + 1, lastvisit = %s
                           WHERE counterID = %s and cookieID = %s''',
                           (cookieID, host, city, useros, browser, ref, lastvisit, cID, cookieID) )
    What is now proved was once only imagined!
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,118
    Rep Power
    4274
    Originally Posted by Nik
    Hello, i'am trying to insert a new record or update an existing one in case counterID(stands for the page's URL) and cookieID(random number) is the same:
    this is perfectly reasonable

    let's start off with just one table, the counters table

    could you do a SHOW CREATE TABLE for this table
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  4. #3
  5. Contributing User
    Devshed Beginner (1000 - 1499 posts)

    Join Date
    Jun 2003
    Location
    Thessaloniki
    Posts
    1,280
    Rep Power
    13
    Originally Posted by r937
    this is perfectly reasonable

    let's start off with just one table, the counters table

    could you do a SHOW CREATE TABLE for this table
    of course, here it is( via phpmyadmin)

    Code:
    show create table counters
    
    CREATE TABLE `counters` (
     `ID` int(5) NOT NULL AUTO_INCREMENT,
     `URL` varchar(100) COLLATE utf8_unicode_ci NOT NULL,
     `hits` int(5) NOT NULL DEFAULT '1',
     PRIMARY KEY (`ID`),
     UNIQUE KEY `URL` (`URL`)
    ) ENGINE=MyISAM AUTO_INCREMENT=6 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
    What is now proved was once only imagined!
  6. #4
  7. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,118
    Rep Power
    4274
    well that looks fine, and your ON DUPLICATE KEY syntax looks fine too

    let's have a look at the other table
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  8. #5
  9. Contributing User
    Devshed Beginner (1000 - 1499 posts)

    Join Date
    Jun 2003
    Location
    Thessaloniki
    Posts
    1,280
    Rep Power
    13
    Originally Posted by r937
    well that looks fine, and your ON DUPLICATE KEY syntax looks fine too

    let's have a look at the other table
    Code:
    show create table visitors;
    
    CREATE TABLE `visitors` (
     `counterID` int(5) NOT NULL,
     `cookieID` smallint(4) NOT NULL,
     `host` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
     `city` varchar(20) COLLATE utf8_unicode_ci NOT NULL,
     `userOS` varchar(10) COLLATE utf8_unicode_ci NOT NULL,
     `browser` varchar(10) COLLATE utf8_unicode_ci NOT NULL,
     `ref` varchar(25) COLLATE utf8_unicode_ci NOT NULL,
     `lastvisit` datetime NOT NULL,
     `hits` int(5) NOT NULL DEFAULT '1',
     UNIQUE KEY `host` (`host`),
     KEY `counterID` (`counterID`)
    ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
    What is now proved was once only imagined!
  10. #6
  11. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2013
    Posts
    158
    Rep Power
    10
    and your ON DUPLICATE KEY syntax looks fine too

    When did it become possible to add a WHERE to an INSERT? :-)

    Comments on this post

    • SimonJM agrees : Good catch
  12. #7
  13. Contributing User
    Devshed Beginner (1000 - 1499 posts)

    Join Date
    Jun 2003
    Location
    Thessaloniki
    Posts
    1,280
    Rep Power
    13
    Originally Posted by Vinny42
    When did it become possible to add a WHERE to an INSERT? :-)
    I didnt knew i couldn't add a where clause at the end.

    The whre clause is for the update statemnt, mening to only update if the where creteris is met, having this effect.

    Code:
    cur.execute('''UPDATE visitors SET cookieID = %s, host = %s, city = %s, useros = %s, browser = %s, ref = %s, hits = hits + 1, lastvisit = %s
                           WHERE counterID = %s and cookieID = %s''',
                           (cookieID, host, city, useros, browser, ref, lastvisit, cID, cookieID) )
    with the ON DUPLICATE KEY can i make the update to happen based on certain criteria?
    What is now proved was once only imagined!
  14. #8
  15. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2013
    Posts
    158
    Rep Power
    10
    The whre clause is for the update statemnt, mening to only update if the where creteris is met, having this effect.

    Have you read the manual on how ON DUPLICATE KEY UPDATE works?
    It is not a normal UPDATE query. When an INSERT causes a DUPLICATE KEY error the database already knows which record caused the duplicate key error and it will run the "update" section of your query on that record.
  16. #9
  17. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,118
    Rep Power
    4274
    Originally Posted by Vinny42
    When did it become possible to add a WHERE to an INSERT? :-)
    there wasn't one on the first INSERT, if you'll notice, since you are replying to me, and the first INSERT is as far as i was able to take it this morning
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  18. #10
  19. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,118
    Rep Power
    4274
    Originally Posted by Nik
    The whre clause is for the update statemnt, mening to only update if the where creteris is met, having this effect.
    you haven't set up the UNIQUE key for this table properly

    you want ON DUPLICATE KEY to match a 2-column UNIQUE key for counterID and cookieID

    then your INSERT will work fine without the WHERE clause
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  20. #11
  21. Contributing User
    Devshed Beginner (1000 - 1499 posts)

    Join Date
    Jun 2003
    Location
    Thessaloniki
    Posts
    1,280
    Rep Power
    13
    Originally Posted by Vinny42
    Have you read the manual on how ON DUPLICATE KEY UPDATE works?
    It is not a normal UPDATE query. When an INSERT causes a DUPLICATE KEY error the database already knows which record caused the duplicate key error and it will run the "update" section of your query on that record.
    I see.
    what iam trying to do is to update an existing visitor based on his retrieved cookie or set a new record if the cookie does nto exist.

    I dont know though what t set as unique index.

    record look like this:

    Code:
    1    rando_cookie_number    somehost
    2    rando_cookie_number    somehost
    3    rando_cookie_number    somehost
    1    rando_cookie_number    somehost
    1    rando_cookie_number    somehost
    Can you help me write it propery?

    Code:
    create table visitors
    ( 
      counterID integer(5) not null,
      cookieID smallint(4) not null,
      host varchar(50) not null,
      city varchar(20) not null,
      userOS varchar(10) not null,
      browser varchar(10) not null,
      ref varchar(25) not null,
      lastvisit datetime not null,
      hits integer(5) not null default 1,
      foreign key (counterID) references counters(ID),
      unique index (cookieID)
    )ENGINE = MYISAM;
    i have it like this at the moment, but i dont know if its correct.

    counterID and cookieID together specify the wedpage and the unique number of a specific visitor.
    That way i know which visitir visted which webpage so i update its countet by increasing 'hits' by one.
    What is now proved was once only imagined!
  22. #12
  23. Contributing User
    Devshed Beginner (1000 - 1499 posts)

    Join Date
    Jun 2003
    Location
    Thessaloniki
    Posts
    1,280
    Rep Power
    13
    Originally Posted by r937
    you haven't set up the UNIQUE key for this table properly

    you want ON DUPLICATE KEY to match a 2-column UNIQUE key for counterID and cookieID

    then your INSERT will work fine without the WHERE clause
    Yes exactly.

    Would that require recreating the tale visitor to look like this:

    Code:
    CREATE TABLE visitors(
    counterID INTEGER( 5 ) NOT NULL ,
    cookieID SMALLINT( 4 ) NOT NULL ,
    host VARCHAR( 50 ) NOT NULL ,
    city VARCHAR( 20 ) NOT NULL ,
    userOS VARCHAR( 10 ) NOT NULL ,
    browser VARCHAR( 10 ) NOT NULL ,
    ref VARCHAR( 25 ) NOT NULL ,
    lastvisit DATETIME NOT NULL ,
    hits INTEGER( 5 ) NOT NULL DEFAULT 1,
    FOREIGN KEY ( counterID ) REFERENCES counters( ID ) ,
    UNIQUE INDEX ( counterID, cookieID )
    ) ENGINE = MYISAM
    and the query to be like this?

    Code:
    INSERT INTO visitors (counterID, cookieID, host, city, useros, browser, ref, lastvisit) VALUES (%s, %s, %s, %s, %s, %s, %s, %s)
    					   ON DUPLICATE KEY(counterID, cookieID) UPDATE cookieID = %s, host = %s, city = %s, useros = %s, browser = %s, ref = %s, hits = hits + 1, lastvisit = %s'''
    What is now proved was once only imagined!
  24. #13
  25. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,118
    Rep Power
    4274
    Originally Posted by Nik
    and the query to be like this?
    no, check the syntax in the manual again, please -- ON DUPLICATE KEY does not allow a list of columns

    and remember, when ON DUPLICATE KEY is triggered, this means you are going to be updating a row instead of inserting it, and, in particular, it means that the value of the unique index already exists, so there would be no point in updating the cookieID column, would there

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  26. #14
  27. Contributing User
    Devshed Beginner (1000 - 1499 posts)

    Join Date
    Jun 2003
    Location
    Thessaloniki
    Posts
    1,280
    Rep Power
    13
    Originally Posted by r937
    no, check the syntax in the manual again, please -- ON DUPLICATE KEY does not allow a list of columns

    and remember, when ON DUPLICATE KEY is triggered, this means you are going to be updating a row instead of inserting it, and, in particular, it means that the value of the unique index already exists, so there would be no point in updating the cookieID column, would there

    Lets start from the beginnign please.
    That would be when we create the visitors table.
    what column must be unique?

    UNIQUE INDEX ( counterID, cookieID ) ?
    Does that make any sense?

    lots of records in the visitors table cna have the same value for counterID, cookieID, hostname but only a single record can have the same value of a specific counterID and aspecific cookieID and a hostname.

    How can i write that at the table creation?
    What columns can and should be unique?
    What is now proved was once only imagined!
  28. #15
  29. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,118
    Rep Power
    4274
    Originally Posted by Nik
    ...only a single record can have the same value of a specific counterID and aspecific cookieID and a hostname.
    really? you're saying that there will be multiple rows for a single combination of counterID and cookieID?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
Page 1 of 2 12 Last
  • Jump to page:

IMN logo majestic logo threadwatch logo seochat tools logo