getting the ids of records affected by an input statement

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

Replies are listed 'Best First'.
Re: getting the ids of records affected by an input statement by JavaFan (Canon) on Nov 22, 2010 at 19:16 UTC
`$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;` [download] Doing error checking (and rolling back if necessary) is left as an exercise for the reader.	[reply] [d/l]
Re^2: getting the ids of records affected by an input statement by Anonymous Monk on Nov 22, 2010 at 23:38 UTC
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?	[reply]
Re^3: getting the ids of records affected by an input statement by JavaFan (Canon) on Nov 22, 2010 at 23:48 UTC
Why do you need a transaction though? 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. 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).	[reply]
Re: getting the ids of records affected by an input statement by locked_user sundialsvc4 (Abbot) on Nov 22, 2010 at 19:22 UTC
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.
Re: getting the ids of records affected by an input statement by grantm (Parson) on Nov 22, 2010 at 22:16 UTC
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.	[reply]
Re: getting the ids of records affected by an input statement by aquarium (Curate) on Nov 22, 2010 at 23:07 UTC
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. the hardest line to type correctly is: stty erase ^H	[reply]