1. Advertising
    y u no do it?

    Advertising (learn more)

    Advertise virtually anything here, with CPM banner ads, CPM email ads and CPC contextual links. You can target relevant areas of the site and show ads based on geographical location of the user if you wish.

    Starts at just $1 per CPM or $0.10 per CPC.

MySQL if exist update else insert

Discussion in 'MySQL' started by kreoton, Apr 26, 2007.

  1. #1
    hi,
    SEMrush
    I whant to write single sql for this:

    if not exists (table colum value=something) insert new row else update table colum value

    i know that MSSQL has this method, but how to do this in mysql?
     
    kreoton, Apr 26, 2007 IP
    SEMrush
  2. abixalmon

    abixalmon Well-Known Member

    Messages:
    378
    Likes Received:
    7
    Best Answers:
    0
    Trophy Points:
    120
    #2
    easy way would be to search that data!
    if the data is found then update it else insert a new row
     
    abixalmon, Apr 26, 2007 IP
  3. ruby

    ruby Well-Known Member

    Messages:
    1,854
    Likes Received:
    40
    Best Answers:
    1
    Trophy Points:
    125
    #3
    ruby, Apr 26, 2007 IP
  4. kreoton

    kreoton Peon

    Messages:
    229
    Likes Received:
    7
    Best Answers:
    0
    Trophy Points:
    0
    #4
    thanks for answer but i'm using 4.1 mysql version
     
    kreoton, Apr 26, 2007 IP
  5. ruby

    ruby Well-Known Member

    Messages:
    1,854
    Likes Received:
    40
    Best Answers:
    1
    Trophy Points:
    125
    #5
    OK try this:

    
    $result = mysql_query("update test set col='test' where col_id='1';");		 
    if (mysql_affected_rows()==0) {
    	$result = mysql_query("insert into test (col_id, col) values ('1','test');");
    }
    
    Code (markup):
     
    ruby, Apr 26, 2007 IP
  6. kreoton

    kreoton Peon

    Messages:
    229
    Likes Received:
    7
    Best Answers:
    0
    Trophy Points:
    0
    #6
    Thanks. I see that there is not any other way so i use this method.
     
    kreoton, Apr 26, 2007 IP
  7. ruby

    ruby Well-Known Member

    Messages:
    1,854
    Likes Received:
    40
    Best Answers:
    1
    Trophy Points:
    125
    #7
    That is the way unless you want to perform a select before you do an update or insert to check if the data exists. That's just a waste of processing though.

    ... my pleasure.
     
    ruby, Apr 26, 2007 IP
  8. TheDataPlanet.com

    TheDataPlanet.com Active Member

    Messages:
    496
    Likes Received:
    5
    Best Answers:
    0
    Trophy Points:
    58
    #8
    Sorry for digging this up, to help those searched through google for a simpler solutions that uses only 1 MySQL query instead of 2 which significantly reduces server load, you can just use REPLACE INTO or ON DUPLICATE KEY UPDATE
     
    TheDataPlanet.com, Aug 8, 2009 IP
  9. nasium

    nasium Active Member

    Messages:
    114
    Likes Received:
    4
    Best Answers:
    1
    Trophy Points:
    90
    #9
    This works for most cases.

    You can also use 'Insert Ignore' in some cases.
     
    nasium, Nov 19, 2009 IP
  10. atag

    atag Peon

    Messages:
    1
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    0
    #10
    Try with REPLACE

     
    atag, Jun 17, 2011 IP
  11. kajol

    kajol Active Member

    Messages:
    523
    Likes Received:
    5
    Best Answers:
    0
    Trophy Points:
    58
    #11
    I think this will not help in all cases. Suppose, if you are going to update the same values... ie, the new value & the old value are same...

    the mysql_affected_rows will return 0.


    When using UPDATE, MySQL will not update columns where the new value is the same as the old value. This creates the possibility that mysql_affected_rows() may not actually equal the number of rows matched, only the number of rows that were literally affected by the query.
     
    Last edited: Jun 29, 2011
    kajol, Jun 29, 2011 IP
  12. sMe76

    sMe76 Peon

    Messages:
    8
    Likes Received:
    2
    Best Answers:
    0
    Trophy Points:
    0
    #12
    Beware that mysql_affected_rows() will return 0 if the value you're updating with is the same as in the database. MySQL is clever and realizes that no update is needed. So you won't know if the row exists or if it already had de same values.

    What I usually do is that I create a column called "updated" as a timestamp. Then i always add updated = '".date('Y-m-d H:i:s')."' in the update query.
    That way you always know that mysql_affected_rows() return 1 if the row exists.
     
    sMe76, Oct 3, 2011 IP
    kind_of_the_cash likes this.
  13. kind_of_the_cash

    kind_of_the_cash Active Member

    Messages:
    853
    Likes Received:
    13
    Best Answers:
    0
    Trophy Points:
    85
    #13
    Thanks a lot finally it worked

     
    kind_of_the_cash, Dec 11, 2011 IP
  14. shibli123

    shibli123 Active Member

    Messages:
    325
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    51
    #14
    great to know you was able to figure it out after so many efforts :) thanks to all the DP members who were so kind sharing all these valuable information about mysql coding :)
     
    shibli123, Jan 5, 2012 IP
  15. freakunleash

    freakunleash Peon

    Messages:
    1
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    0
    #15
    sorry to ask on existing thread, but how will you use this query when you are getting input value as an array.
     
    freakunleash, Jan 10, 2012 IP
  16. kind_of_the_cash

    kind_of_the_cash Active Member

    Messages:
    853
    Likes Received:
    13
    Best Answers:
    0
    Trophy Points:
    85
    #16

    Use fetch_array with while

    while($row = mysql_fetch_array($result)){
    // code here
    }
     
    kind_of_the_cash, Jan 10, 2012 IP
  17. tittbit

    tittbit Active Member

    Messages:
    161
    Likes Received:
    1
    Best Answers:
    0
    Trophy Points:
    75
    #17
    older sql version do not have this functionality.

    i think the 5.xx or higher would be working for it
    which version are u on for ur server
     
    tittbit, Feb 2, 2012 IP