Beefy Boxes and Bandwidth Generously Provided by pair Networks
Welcome to the Monastery

mysql insert/update

by rsiedl (Friar)
on May 31, 2004 at 09:57 UTC ( #357759=perlquestion: print w/replies, xml ) Need Help??
rsiedl has asked for the wisdom of the Perl Monks concerning the following question:

This is more of a MySQL question, but I am sure someone out there will know an answer.

Is there and easy way to insert/update a record in a mysql table depending upon whether or not the record exists?

if ($id exists in table) { update field=value where id=$id } else { insert into table id=$id, field=$value }

Hope that makes sense :)
At the moment I am doing a select and then using a perl if statement to determine if it should update or insert.
But I am sure there must be an easier way for such a common thing.

Any help would be appreciated.


Replies are listed 'Best First'.
Re: mysql insert/update
by liz (Monsignor) on May 31, 2004 at 10:05 UTC
      Thanks Liz,
      Not 100% what I was after but has put me onto "ON DUPLICATE KEY UPDATE".
      Now I just need to decide if I should update my MySQL :)

      Update: My reasons for not using REPLACE is from my understanding it will delete the row and re-insert it with any column that I dont specify a value for, becoming the default value. I am after something where I am able to just update one column value keeping the others in there also.
    Re: mysql insert/update
    by Happy-the-monk (Abbot) on May 31, 2004 at 10:03 UTC

      Yes, either you do a select first to find out if the row exists, or you just go ahead with an insert, and should that fail for the reason you expected, follow up with the update statement. Or the other way round. Quick and dirty.

      Cheers, Sören

    Re: mysql insert/update
    by dkr (Initiate) on May 31, 2004 at 22:46 UTC
      'update;insert if update did not update' makes the most sense if the majority of your queries are updates; it means that most of the time you are doing a single query, whereas 'select;insert or update based on select result' means you always do two queries regardless.

      The only problem is that mysql has an optimization such that if you update a row with the values it already has, it will not do the update and return 0 rows modified. I work around this by using the mysql_client_found_rows option. That tells mysql to always return the number of rows matched.

      my $dbh = DBI->connect( "DBI:mysql:database=$db_name;host=$DB_HOST;mysql_client_found_rows=1 +", "$DB_USER", "$DB_PASS" ) or die $DBI::errstr; my $rows = $dbh->do(qq~ update mytable set myvalue = ? where mykey = ? ~, undef, $myvalue, $mykey ) or die $DBI::errstr; if ($rows == 0) { # no record updated, so we insert new one $dbh->do(qq~ insert into mytable values ( ?, ? ) ~, undef, $mykey, $myvalue ) or die $DBI::errstr; }
      Note that do() returns 0E0 which == 0, but is still true. Also note that when using this method, the order of the bound vars are often swapped in the insert since the key or keys are last in the update sql. This code is also cool because it says 'do or die', ;)

    Log In?

    What's my password?
    Create A New User
    Node Status?
    node history
    Node Type: perlquestion [id://357759]
    Approved by neniro
    and all is quiet...

    How do I use this? | Other CB clients
    Other Users?
    Others imbibing at the Monastery: (2)
    As of 2018-02-21 23:46 GMT
    Find Nodes?
      Voting Booth?
      When it is dark outside I am happiest to see ...

      Results (288 votes). Check out past polls.