mysql insert/update

[rsiedl]

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?

i.e.
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.

Cheers,
Reagen

[liz]

See REPLACE.

Liz

[rsiedl]

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 :)
Cheers,
Reagen

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.

[Anonymous Monk]

[Happy-the-monk]

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

[dkr]

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