SitePoint Sponsor

User Tag List

Results 1 to 7 of 7
  1. #1
    SitePoint Zealot
    Join Date
    Jan 2009
    Posts
    144
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    If Not Exist Update Else Insert

    I am trying to make a right syntax but I don't know how. Please help me to correct it:

    PHP Code:
    mysql_query("IF NOT EXISTS (INSERT INTO users (firstName, lastName, id2, gender) VALUES ('Nick', 'AAAA', '4', 'm')) ELSE (UPDATE users SET firstName='Nick', lastName='AAAA', id2='4', gender='m' WHERE ID='4')") or die (mysql_error());

    //or

    mysql_query("IF EXISTS (UPDATE users SET firstName='Nick', lastName='AAAA', id2='4', gender='m' WHERE ID='4') ELSE (INSERT INTO users (firstName, lastName, id2, gender) VALUES ('Nick', 'AAAA', '4', 'm'))") or die (mysql_error()); 
    Last edited by meee; Feb 10, 2009 at 08:22.

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    see INSERT syntax in da manual
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    ... and check out the part that explains ON DUPLICATE KEY UPDATE

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  4. #4
    Programming Since 1978 silver trophybronze trophy felgall's Avatar
    Join Date
    Sep 2005
    Location
    Sydney, NSW, Australia
    Posts
    16,875
    Mentioned
    25 Post(s)
    Tagged
    1 Thread(s)
    A simpler way to do that would be to use a REPLACE statement instead of the INSERT/UPDATE.
    Stephen J Chapman

    javascriptexample.net, Book Reviews, follow me on Twitter
    HTML Help, CSS Help, JavaScript Help, PHP/mySQL Help, blog
    <input name="html5" type="text" required pattern="^$">

  5. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    there is a subtle difference between REPLACE and INSERT... ON DUPLICATE KEY UPDATE
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  6. #6
    SitePoint Guru
    Join Date
    Jun 2006
    Posts
    638
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    there is a subtle difference between REPLACE and INSERT... ON DUPLICATE KEY UPDATE
    "subtle" but important on big tables.

    REPLACE: deletes then inserts a record, so redoes indexes.
    INSERT ON DUPLICATE UPDATE: If you don't update an indexed field, it will not touch the indexes (so faster)

  7. #7
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    "subtle" because of the following difference --

    REPLACE deletes then inserts; there's no good way to increment a column

    INSERT ON DUPLICATE KEY UPDATE can increment, and furthermore can be incredibly useful for multiple-row inserts
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

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