distinct update

[newtosql0001]

I need to update one field in a table, there are multpible instances of my criteria however, I only want one field updated and the rest to be skipped.
update CLAIMS set field1 = 'ready' WHERE attorney = 'Doe'
I can use the following query to find the cases that need to be updated
select distinct
caseid
from CLAIMS
WHERE attorney = 'Doe'
the problems arises because an attorney "Doe" may appear in a case once, or maybe 50 times and I don't want "ready" to appear in all 50 claims, just one.

would i bring my distinct query into a new table, make the update and then bring that table back in?

 

[foxdev]

How about this:

[tt]set rowcount 1
update CLAIMS set field1 = 'ready' WHERE attorney = 'Doe'
set rowcount 0
[/tt]

 

[carrr]

You must have a unique identifier somewhere, right?
By this I mean, maybe 'Doe' appears multiple times within a given case (presuming your case is your primary key), but is there another field within the information on that case that you can add to your criterion for your update.
Ex:

WHERE attorney = 'Doe'
AND date = 'xx/xx/xxxx'

If this isn't the case, you might consider going back and adding a column to your table that can help you to isolate rows that might share the same identifier, creating a compound key, i.e.:

Case # SubID
12345 1
12345 2
12345 3
12345 4

Hope this might help.

 

[newtosql0001]

I tried
set rowcount 1
update CLAIMS set field1 = 'ready' WHERE attorney = 'Doe'
set rowcount 0
this affects only 1 record, the first one
thanks foxdev for the help

carrr
as far as I know this table does not have a one field unique identifier, the case is unique in another table however, in this table you can't have case, claim, code all the same i believe but these number are not 1,2,3,4,5 etc
could you explain more on how to create a compound key? this sounds like it would work

 

[carrr]

Sure. Let me ask you, if you would, to give me a rundown of your table layout. What are your field names? Also, how many rows are in your table? Just trying to figure out the best way to steer you.