finding first record ... !?

[Richard C Buchanan]

I have a table as follows:

- user_id (key)
- user_email_address (text)
- user_request_date (text)
- user_sent_date (time/date)

An automated macro will run every 5 minutes and needs to run a SQL UPDATE
that:

- finds the FIRST (ONE ROW ONLY) (youngest) record where
- user_request_date is the most recent
- AND user_sent_date is NULL (i.e. blank)

- THEN UPDATES the user_sent_date for THAT record to NOW() (i.e. the
current system date/time)
For example:

- record 33 - request_date 01-JUL-03 14:55:03 - user_sent_date = NULL
- record 34 - request_date 01-JUL-03 15:03:46 - user_sent_date = NULL
- record 35 - request_date 01-JUL-03 16:08:08 - user_sent_date = NULL
- record 36 - request_date 01-JUL-03 16:57:31 - user_sent_date = NULL
- record 37 - request_date 01-JUL-03 17:11:41 - user_sent_date = NULL

SO - the above NEEDED SQL UPDATE would update ONLY row 33 (the FIRST ROW)

- update record 33 - with NOW() time and date
THEN record 34, then 35, then 36, then 37
ONE ROW at a time per each SQL UPDATE

Any ideas!?
Thanks!
Richard

richard @ rcbuchanan . com

[Steve Kass]

Richard,

Try something like

update Richard set
user_sent_date = getdate()
where user_id = (
select top 1 user_id from Richard R
where R.user_sent_date is null
order by user_request_date
)

Your narrative and your example don't agree on whether
you want the most recent (youngest), or least recent (oldest)
user_request_date, so you may need to add DESC to the
order by clause.

Steve Kass
Drew University

Richard C Buchanan wrote:

I have a table as follows:

- user_id (key)
- user_email_address (text)
- user_request_date (text)
- user_sent_date (time/date)

An automated macro will run every 5 minutes and needs to run a SQL UPDATE
that:

- finds the FIRST (ONE ROW ONLY) (youngest) record where
- user_request_date is the most recent
- AND user_sent_date is NULL (i.e. blank)

- THEN UPDATES the user_sent_date for THAT record to NOW() (i.e. the
current system date/time)
For example:

- record 33 - request_date 01-JUL-03 14:55:03 - user_sent_date = NULL
- record 34 - request_date 01-JUL-03 15:03:46 - user_sent_date = NULL
- record 35 - request_date 01-JUL-03 16:08:08 - user_sent_date = NULL
- record 36 - request_date 01-JUL-03 16:57:31 - user_sent_date = NULL
- record 37 - request_date 01-JUL-03 17:11:41 - user_sent_date = NULL

SO - the above NEEDED SQL UPDATE would update ONLY row 33 (the FIRST ROW)

- update record 33 - with NOW() time and date
THEN record 34, then 35, then 36, then 37
ONE ROW at a time per each SQL UPDATE

Any ideas!?
Thanks!
Richard

richard @ rcbuchanan . com