DBI and UPDATE

Anonymous Monk has asked for the wisdom of the Perl Monks concerning the following question:

Hi, folks!
I know I can use placeholders for INSERT statements, like here:

my $dbh = DBI->connect(blah) or die $DBI::errstr;
my $sth = $dbh->prepare('INSERT INTO MYTABLE(col1 col2) VALUES (?,?)')
+;
$sth->execute(@data);
[download]

Is there a way to do the same thing with UPDATE? how would I do something like:

my $dbh = DBI->connect(blah) or die $DBI::errstr;
my $sth = $dbh->prepare('UPDATE MYTABLE SET col1=?,col2=?');
$sth->execute(@data);
[download]

Thanks in advance!

Comment on DBI and UPDATE Select or Download Code

Replies are listed 'Best First'.
(Ovid) Re: DBI and UPDATE by Ovid (Cardinal) on Dec 13, 2001 at 01:35 UTC
If you're careful, you can simulate this... `my $dbh = DBI->connect(blah) or die $DBI::errstr; my $sth = $dbh->prepare('UPDATE MYTABLE SET col1=?,col2=?'); $sth->execute(@data);` [download] ...with this... `my $dbh = DBI->connect(blah) or die $DBI::errstr; my $sth = $dbh->prepare('UPDATE MYTABLE SET col1=?,col2=?'); $sth->execute(@data);` [download] :-) In other words, that code looks fine to me. Have you tried it? If so, what is your error? If it's not working, post a code snippet, the error, and the database you're using. Also, did you intentially leave out a `WHERE` clause? Without it, you're going to update every row in the table. Probably not what you wanted. Cheers, Ovid Join the Perlmonks Setiathome Group or just click on the the link and check out our stats.	[reply] [d/l] [select]
Re: DBI and UPDATE by twerq (Deacon) on Dec 13, 2001 at 01:32 UTC
From reading man DBI, it looks like you should be able to do exactly that... but if you can't, there's always the option of resorting back to $dbi->quote() for your update statement.. --twerq	[reply]
Re: DBI and UPDATE by Anonymous Monk on Dec 13, 2001 at 02:56 UTC
Thanks everyone! I guess I should've tried it first. It really does work. :)	[reply]
Re: Re: DBI and UPDATE by mpeppler (Vicar) on Dec 13, 2001 at 03:45 UTC
Just remember to use a WHERE clause to only update the data in the appropriate rows - as written all the rows in that table get updated... Michael	[reply]
Re: DBI and UPDATE by Steve_p (Priest) on Dec 13, 2001 at 02:08 UTC
What you have down for the update should work fine. I know you may have excluded some things for the example, but make sure you are checking for errors in the prepare and execute.	[reply]