MySql and perl update else inset problem

I need to update a value if it exists, but create a new row if it doesn't

[fabricator]

Kind of urgent I get a solution to this, at the moment I'm inserting new rows by hand using phpmyadmin. That won't be possible soon though.

my $sth = $dbh->prepare(qq{
UPDATE `data` SET value='$value' WHERE (object='$object')
});
$sth->execute;

$failed =?; # what goes here?
if ($failed) {
my $sth = $dbh->prepare(qq{
INSERT INTO `data` (`id`,`object`,`value`) VALUES (NULL,'$object','$value')
});
$sth->execute;
}

Google is returning page after page of useless results, mostly idiots guides to MySql statements with the remotely complex stuff removed.

[physics]

$sth->execute;

$failed =?; # what goes here?
if ($failed) {

I think you should try

if(! $sth->execute ){ 
... 

I'm not sure but it seems like the simplest solution might be to add a unique key where appropriate and then use INSERT ... ON DUPLICATE KEY UPDATE [dev.mysql.com]

Or you might just first SELECT id FROM data WHERE ...
Then if that id > 0 do the update, else do the insert.

[fabricator]

I found I had to use this.

if ($sth->execute == 0){
# insert statement

$sth->execute returns the number of rows updated, well thats what the example I found uses it for.

[andye]

Google is returning page after page of useless results, mostly idiots guides to MySql statements with the remotely complex stuff removed.

You want to be using the MySQL docs site, it's very useful.

[dev.mysql.com...]

A.

[fabricator]

physics posted the same link earlier actually.

Actually I found the MySQL docs site to be pretty useless as far as usable documentation goes, you can't save the pages, and they aren't printer friendy either. I can't see a downloadable archive anywhere either.

[dev.mysql.com...]

Says you can print a copy for personal use or convert it to other formats as long as the content itself isn't altered. But you can't as the pages don't save.

Anyway the if statement first more into the rest of my codeing, I have to add history to some fields (ie use INSERT even if the row exists already)

[phranque]

Actually I found the MySQL docs site to be pretty useless as far as usable documentation goes, you can't save the pages, and they aren't printer friendy either. I can't see a downloadable archive anywhere either.

pdf version [downloads.mysql.com]
zipped html [downloads.mysql.com]
many other version available for download [dev.mysql.com]

[andye]

Worst thing about the MySQL docs site is that the search is terrible - I always use a Google 'site' search instead.

Apart from that though, I find them pretty well-written and so on.

If you're after printed docs, you can get the whole thing in book format (I think - the one I have is for a few versions ago, but I'd be surprised if they haven't updated it).