Since James wants a way to get a unique record from any target database
file that does or does not have a unique key field combination, there
has to be a "where" clause involved somewhere, to update one record.
The WHERE clause will have specific values for specific columns, but
there are other columns in that record that might make it a logically
different row. To my knowledge, SQL standards provides no exact way
other than a unique key identifier to enforce that using the same values
in a WHERE clause will get the same /exact/ record.
So one alternative is to pick up the entire record with all columns when
you fetch it, then when you go back either add them to the WHERE clause
(cumbersome) or do a cursor and result set to read through all the rows
that match and compare the values (also cumbersome). If all the non-key
or non-WHERE values are the same then it makes no difference at all
which one you get.
That's life in SQL without being sure you can use the RRN(YourFile)
function.
Of course for the database you control, you can always also use the
Identity column function. I think that's a standard, at least it's used
in others I've seen, although other databases have different syntax for
it. (What else is new?)
You can also add a sequencing column for a result set, I believe, that
disappears later with the result set. But I'm not so sure you could
fetch the values, look at them, and then update them using that number.
If you get real sophisticated and your budget approvers sign off, you
could use the SQLCLI function also to automate the checking of all the
values in a return set and using all the columns in a dynamically
generated WHERE clause to update the record. I've been itching to write
it, but it's involved and will take time.
On 1/1/13 6:26 AM, D*B wrote:
<quote>So we want to be able to display records without acquiring update locks on them, but to be able to go back into any one those records with absolute certainty that it's the right record, acquire an update lock, and update it. </quote>
If the table has duplicate records, you can't ensure to update a specific record of duplicate rows. The only way to give a resultset a specific order is an order by clause, but duplicates could not be ordered and pulling the same Resultset twice, you could get the duplicates in another sequence (e.g.: a tablescan might be implemented in parallel on a machine with multiple CPUs)
D*B
midrange.com
