comment on

'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;
}
[download]

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', ;)

In reply to Re: mysql insert/update by dkr
in thread mysql insert/update by rsiedl

Are you posting in the right place? Check out Where do I post X? to know for sure.
Posts may use any of the Perl Monks Approved HTML tags. Currently these include the following:
<code> <a> <b> <big> <blockquote> <br /> <dd> <dl> <dt> <em> <font> <h1> <h2> <h3> <h4> <h5> <h6> <hr /> <i> <li> <nbsp> <ol> <p> <small> <strike> <strong> <sub> <sup> <table> <td> <th> <tr> <tt> <u> <ul>
Snippets of code should be wrapped in <code> tags not <pre> tags. In fact, <pre> tags should generally be avoided. If they must be used, extreme care should be taken to ensure that their contents do not have long lines (<70 chars), in order to prevent horizontal scrolling (and possible janitor intervention).
Want more info? How to link or How to display code and escape characters are good places to start.


XP is just a number
	PerlMonks