MySql and perl update else inset problem

Forum Moderators: open

Message Too Old, No Replies

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

9:40 am on Jun 10, 2007 (gmt 0)

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

9:26 pm on Jun 10, 2007 (gmt 0)

$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

12:29 pm on Jun 11, 2007 (gmt 0)

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

1:29 pm on Jun 11, 2007 (gmt 0)

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...]

fabricator

4:04 pm on Jun 11, 2007 (gmt 0)

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

10:56 pm on Jun 11, 2007 (gmt 0)

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

12:05 pm on Jun 12, 2007 (gmt 0)

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).