UPDATE TOP (1)?

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.

Marina
Thu Aug 02 14:35:30 CDT 2007

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@hotmail.com> 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.
>

Terry
Thu Aug 02 16:25:43 CDT 2007

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@someplace.com> 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" <tolsen64@hotmail.com> 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.
>>
>
>

Terry
Thu Aug 02 16:57:29 CDT 2007

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@hotmail.com> 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" <someone@someplace.com> 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" <tolsen64@hotmail.com> 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.
>>>
>>
>>
>
>