I'm using the OLEDB provider against an Access Database. I need to update
just one record. Is there any way to do something like this?

UPDATE TOP 1 [Articles] SET [Status]='MyClientID' WHERE [Status]=''

I only want to update one record, because i'm going to select that record in
the next statement by using:

SELECT * FROM [Articles] WHERE [Status]='MyClientID'

Any suggestions or guidance?

Thanks.

You are saying you want to update any record where the Status is blank, you
don't care which, so long as it is just one?

That sounds like bad database design. You should have a primary key that
never changes, select top 1 and get the primary key of one record, then use
that in the WHERE for your update. You can then use the same key for your
SELECT later.

"Terry Olsen" <tolsen64 (AT) hotmail (DOT) com> wrote

I have several threads (clients) that will be querying the database to get
records. I need to update that record FIRST to show that it's in use by a
client. That way none of the other clients will select it. If I do it the
way you say, it's possible that another client will get in there and select
the same record.

Table layout is:
ID as PrimaryKey
ArticleID as Text
Status as Text

My method, if I can get it to work, or find a better method is:

Client updates first record with an empty Status field with it's ClientID.
Client then selects the record WHERE Status=it's ClientID
Client works on the ArticleID from that record...
Client then updates the record's Status to 'Finished'
Repeat process.


"Marina Levit" <someone (AT) someplace (DOT) com> wrote

Heh..got it to work. Here's the SQL Statement.

UPDATE [Articles] SET [Status]='test' WHERE ID=(SELECT TOP 1 [ID] FROM
[Articles] WHERE [Status]='')


"Terry Olsen" <tolsen64 (AT) hotmail (DOT) com> wrote