getting the ids of records affected by an input statement

[Anonymous Monk]

dear monks, How do i get the ids of all of the records affected by an input statement?

My code is like this:

$insertstr = ("update table set col1= ? where col2 = ?);
$rows_affected = $dbh->do($insertstr, undef, $val1, $val2)

I am using MYSQL I can't post my code for confidentiality reasons but that is basically the crix of my problem.
thanks a lot for any help

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

[sundialsvc4]

You can also begin a transaction, query to find out the IDs that would be affected by the update, then perform the update and commit. You must use a transaction isolation-level that is sufficent to assure that there can be no competing interference from other parallel processes – a prospect that can be quite expensive.

If at all possible, use the strategy suggested by the first reply:   “find the records that now have the target-value, whether they were just-updated-by-me or not.”   When you are querying a database, you really want to arrange your workflows such that you only are concerned about “how the data now is,” not “exactly how it got there.”   (Obviously not referring to auditing-considerations when I say that...)   Design clean, low-flying operations that play nicely with others and do not demand high levels of transaction isolation.

If you can do a whole bunch of updates and then query, after all is said and done, “(now that I have finished doing everything that I intended to do, and committed those changes,) show me all the records which now have any of the following values for this field...” then, that is better yet.

[grantm]

The SQL spec allows an UPDATE statement to have a 'RETURNING' clause, which will (I think) do exactly what you want. Unfortunately it's not implemented in MySQL. It's been in Postgres since version 8.2 and is documented here.

[aquarium]

failing any provisions any particular db provides returning affected rows, or even db logs...you could add a column to the table that will hold your marker for your updates. Each time you do the update, programmatically pick a value that will be used in the update. after some updates, you will be able to query the db for the value(s) in that column. this guarantees atomicity with your update statement, so doesn't suffer latency issues if you do a select and then the equivalent update. I'd only do this (extend the table) if your requirement is part of the "business rules", and hence part of the specification for the database schema.