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)


Return to Archive home page | Return to MIDRANGE.COM home page