Re: getting the ids of records affected by an input statement

[JavaFan]

  $dbh -> begin_work;
  my $ids = $dbh -> selectall_arrayref("SELECT id FROM table WHERE col
+1 != ? AND col2 = ?", undef, $val1, $val2);
  $dbh -> do("update table set col1= ? where col2 = ?", undef, $val1, 
+$val2);
  $dbh -> commit;

Doing error checking (and rolling back if necessary) is left as an exercise for the reader.

[Anonymous Monk]

Thanks for your reply. I can see that a 'workaround' is to do the select and find out the ids that would be affected and then do the updae. Why do you need a transaction though?

[JavaFan]

Why do you need a transaction though?

1. You're modifying the database. Not putting a database modification inside a transaction is far more unforgivable than not ever using strict, warnings or checking the return values of system calls. In fact, if it were my call, not putting database modifications inside transactions would put you on the fast track to getting fired.
2. You do not want the state (or to be precise, your view) of the database to actually change between the select and the update. Hence the transaction. (I'm assuming you have a proper default isolation level active).