Re: Uniquely identifying a record in SQL without a unique key?

[aec <cfuture@xxxxxxxxxxx>]

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