First I'd deactivate the old 2.
UPDATE tablename
SET active = 'NO'
WHERE active = 'YES'
I think you'll have to fill us in on why 33, and 12 "meet the criteria".
I guess if you have a nice query you use to get the 2 records you want
to update you can plug that query into:
UPDATE tablename
SET active = 'YES'
WHERE id IN (SELECT id
FROM tablename
WHERE...?)
Or use CF to do it. Say you have 2 ids from a query "get_active".
<cfloop query="get_active">
<cfquery name="update" datasource="my_dsn">
UPADTE tablename
SET active = "YES"
WHERE id = #id#
</cfquery>
</cfloop>
-----Original Message-----
From: W Luke [mailto:[EMAIL PROTECTED]]
Sent: Friday, September 20, 2002 10:10 AM
To: CF-Talk
Subject: Finding next 2 items in query
Hi,
I need to run a scheduled task which updates a table in my DB. The
structure looks something likes this:
----------------------
ID* - Active - Approved
----------------------
12 - No - Yes
23 - No - No
24 - No - Yes
29 - Yes - Yes
32 - Yes - Yes
33 - No - Yes
40 - No - No
* (AutoNumber field)
What I need to do is update the next 2 IDs in a table, and "de-activate"
the current 2. In this example the current Active records are 29 and
32. I would need to update this table and make IDs 33 and 12 "active,"
because they are the next 2 which meet the criteria. There are various
other clauses, but for the time being I'll keep it simple - WHERE
Approved = Yes. ID 40 is yet to be approved.
I'm not really sure how to do this - can anyone give me some pointers?
The DB is in Access...
Many thanks
Will
______________________________________________________________________
Signup for the Fusion Authority news alert and keep up with the latest news in
ColdFusion and related topics. http://www.fusionauthority.com/signup.cfm
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Archives: http://www.mail-archive.com/[email protected]/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
