SQL Update query

[atuvy]

I am using VB6 and SQL 2000 server, with the code below I select records from a table (records are identical) and I want to update only one record, for some reason it updates all of the records which were selected in the query, please help

Set rst = Nothing
Set rst = New ADODB.Recordset
strSQL = "select * from Messages where Id ='27'"
rst.Open strSQL, cnn, adOpenKeyset, adLockOptimistic
If rst.RecordCount > 0 Then
rst.MoveFirst
If rst.Fields("NumOfTry") < MAX_NUM_RETRY Then
rst.Fields("Processed") = SET_IN_PROCESS
'Increment Counter
nTry = rst.Fields("NumOfTry") + 1
rst.Fields("NumOfTry") = nTry
rst.Update
end if
end if

[KrisSimonis]

Update statements will always update all records that evaluate the WHERE condition as true. So if your records are identical, both will be updated. If you wish to update only one, you must uniquely identify it.
This is why UNIQUE constraints are used. Else you cannot pick one record, only groups.
IE.
Consider the following table:

TABLE A
ID = int
Status = Boolean
Counter = int


Now we're going to put records in it:

ID Status Counter
0 TRUE 4
1 FALSE 2
2 FALSE 7
2 FALSE 7


Because we did not say that our ID field had to be unique, we now have 2 records in here with the same ID. Meaning you cannot tell the difference between the two of them, and as such, SQL 2000 will always update both of them when you try to update one of them.
by forcing the UNIQUE constraint in your table, the 2nd ID = 2 record will not be admitted to the table ( it should be in the table as 3, using an IDENTITY integer as key column is generally an easy way to do this )
and when we want to update 1 record, we can tell EXACTLY which one because we can identify it among all the others 'Beyond reasonable doubt'

[atuvy]

Thanks, the only field that is unique is a filed that contains data which is entered automaticaly and is of timestamp data type. The data in that column looks like 0x000000001A6D3CF and I always want to select the record with the smallest value. At the time that I issue the query I do not know what the value is, how do I issue such a query?

[KrisSimonis]

Not very difficult, you use the MIN operator. Going from the original table I made and adding the timestamp:


TABLE_A
ID INT
Counter INT
Processed BOOLEAN
time TIMESTAMP UNIQUE

ID Status Counter TimeStamp
0 TRUE 4 0x23901847
1 FALSE 2 0x894753d3
2 FALSE 7 0x34095712
2 FALSE 7 0x34985734

SELECT TOP 1 ID, Status, Counter, MIN(Timestamp)
FROM TABLE_A
WHERE ID = '27'
GROUP BY ID, Status, COUNTER


This will give you the first stamped record with ID = '27'. If you want to know exactly how it works, I suggest you read the basic SQL 2000 help files they got very good examples on how this works. Just open the query analyzer and hit F1 on the MIN statement.

[atuvy]

Thanks