UPDATE TOP (1)?

  • Thread starter Thread starter Terry Olsen
  • Start date Start date
T

Terry Olsen

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.
 
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 said:
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 said:
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.
Click to expand...
Click to expand...
 
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 said:
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 said:
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 said:
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.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top