#1
  1. Contributing User
    Devshed Novice (500 - 999 posts)

    Join Date
    Nov 2003
    Posts
    754
    Rep Power
    123

    Insert ... On Duplicate Key ... Where...


    I would like to insert a new row, but when I update I have a where.
    PHP Code:
    INSERT INTO reports (idlast_nameVALUES (622811'A Name'
    ON DUPLICATE KEY UPDATE school 12homeroom '2' WHERE delete_me 'N' 
    Is is possible to do this via an sql statement or can it be done via some unique index?
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,678
    Rep Power
    4288
    i cannot understand what you're trying to accomplish

    please do a SHOW CREATE TABLE for your table
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  4. #3
  5. Contributing User
    Devshed Novice (500 - 999 posts)

    Join Date
    Nov 2003
    Posts
    754
    Rep Power
    123
    PHP Code:
    SHOW CREATE TABLE reports

    CREATE TABLE 
    `reports` (
      `
    idint(11NOT NULL,
      `
    last_namevarchar(20NOT NULL,
      `
    schoolint(11NOT NULL,
      `
    homeroomvarchar(20NOT NULL,
      `
    delete_mechar(1NOT NULL DEFAULT 'Y',
      
    PRIMARY KEY (`id`)
    ENGINE=MyISAM DEFAULT CHARSET=latin1 
    I want to insert new rows, but if there is a duplicate row, I would want to update it only if delete_me is 'N'.
  6. #4
  7. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,678
    Rep Power
    4288
    Originally Posted by epanagio
    but if there is a duplicate row...
    you haven't defined a UNIQUE key, which is what you need in order for the ON DUPLICATE KEY to work

    technically, `id` being the PK is also unique, but really, are you somehow afraid you might be generating duplicate id values?

    also, how come you aren't inserting values for `school`, `homeroom`, and `delete_me`? they're missing DEFAULT values!!!!
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  8. #5
  9. Contributing User
    Devshed Novice (500 - 999 posts)

    Join Date
    Nov 2003
    Posts
    754
    Rep Power
    123
    I thought the the PK id would be sufficient. The field id is a unique field.

    The correct insert:
    PHP Code:
    INSERT INTO reports (idlast_nameschoolhomeroomdelete_meVALUES (622811'A Name'2'123''Y')  
    ON DUPLICATE KEY UPDATE school 12homeroom '2' WHERE delete_me 'N' 
    Later on the value of the field delete_me may change from a 'N' to a 'Y'. If I add a new unique index having id and delete_me, I may end up with two rows with the same id and different delete_me values.
    I must make sure that I have only one id.
    Also, the values of the fields: school and homeroom will vary. The on duplicate key values for school and homeroom will vary also.
  10. #6
  11. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,678
    Rep Power
    4288
    Originally Posted by epanagio
    I thought the the PK id would be sufficient.
    i think you'd better do a bit more research on how ON DUPLICATE KEY is intended to be used

    an integer id is typically not what should be triggering it

    as i said, do you really think you might be adding a duplicate id? how are these numbers generated?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  12. #7
  13. Contributing User
    Devshed Novice (500 - 999 posts)

    Join Date
    Nov 2003
    Posts
    754
    Rep Power
    123
    The field id is a system generated number and it is similar to a social security number where there is only one number per person. It is guaranteed to be unique.
  14. #8
  15. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,678
    Rep Power
    4288
    Originally Posted by epanagio
    It is guaranteed to be unique.
    so how come you were expecting to handle an attempted duplicate id being inserted????
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  16. #9
  17. Contributing User
    Devshed Novice (500 - 999 posts)

    Join Date
    Nov 2003
    Posts
    754
    Rep Power
    123
    Good question. Sometimes I have to get in there in order to handle special cases and I want to lock the record and not have it updated. You know, last minute requests.
    I added the field delete_me to prevent that row from being changed or deleted.

IMN logo majestic logo threadwatch logo seochat tools logo