Results 1 to 4 of 4
  1. #1
    Join Date
    Feb 2005
    Location
    Mumbai, India
    Posts
    161

    Unanswered: Using SET in INSERT ON DUPLICATE KEY UPDATE

    Hi

    How do I get this to work ?
    Code:
    INSERT INTO `tbl`
    SET `PK`=3, `FK`=1, `Data1` = 'data1', `Data2` = 'data2'
    ON DUPLICATE KEY UPDATE SET `FK`=1, `Data1` = 'data1', `Data2` = 'data2' 
    WHERE PK`=3
    I need to use SET instead of VALUES() for INSERT because `FK`=1, `Data1` = 'data1', `Data2` = 'data2' is actually one long assignment line and it would be used from a PHP variable $set. So it'll turn out like this :
    Code:
    INSERT INTO `tbl`
    SET `PK`=3, $set
    ON DUPLICATE KEY UPDATE SET $set WHERE PK`=3
    Thanks
    MySQL 5.1

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    so whats stopping you using conventional standards compliant SQL?

    Why do you need to use SET?

    whats wrong with you building you insert statement within your PHP string
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Feb 2005
    Location
    Mumbai, India
    Posts
    161
    If I got to use INSERT INTO tble ('f1', 'f2', 'f3') VALUES ('a', 'b', 'c') then I got to make a $set2 to create this assignments.
    I was hoping to use the same $set for INSERT and UPDATE ON DUPLICATE KEY.
    MySQL 5.1

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    remove the word SET after the word UPDATE, and remove the WHERE clause after the word UPDATE
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •