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"  wrote in message 
news:ecndPsT1HHA.4184@TK2MSFTNGP06.phx.gbl...

> 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.
> 

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"  wrote in message 
news:uhEPKxT1HHA.5772@TK2MSFTNGP02.phx.gbl...

> 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"  wrote in message 
> news:ecndPsT1HHA.4184@TK2MSFTNGP06.phx.gbl...
>> 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.
>>
>
> 

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"  wrote in message 
news:%230fJuuU1HHA.600@TK2MSFTNGP05.phx.gbl...

>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"  wrote in message 
> news:uhEPKxT1HHA.5772@TK2MSFTNGP02.phx.gbl...
>> 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"  wrote in message 
>> news:ecndPsT1HHA.4184@TK2MSFTNGP06.phx.gbl...
>>> 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.
>>>
>>
>>
>
>