Advanced Insert Statements
So, let's say you have an application with (oh the horror) users. This application keeps track of a user status. Let's say this is a sales application. For the sake of simplicity we can assume that there are two statuses, 'active' and 'on hold'. They might be stored in a table like this:
Statuses (StatusID, Status)
And User Status Information, might be in a table like this:
UserStatusHistory(UserSTatusHistoryID, UserID, StatusID, StatusDate, endDate)
Why did I put an end date in there? For the purpose of this example we're going to assume that the "On hold" status can expire. When the status expires, the system should automatically change the user status back to Active. You'd implement that with a scheduled task, right? I would too! But, what is the code?
The easiest end route to the problem is something like this:
<cfquery>
select UserStatusHistory.*
from UserStatusHistory
where UserStatusHistory.StatusID = [on hold ID] and
userstatushistory.userstatushistoryID in (select top 1 ushsub.userstatushistoryID
from userstatushistory ushsub
where ushsub.userID = userstatushistory.userID
order by statusdate desc
) and
UserStatusHistory.enddate <= GETDATE()
</cfquery>
<cfloop query>
<cfquery>
insert into userstatushistory (UserID, StatusID, StatusDate, endDate)
values(query.userID, [active status], now(), null)
</cfquery>
</cfloop>
The code does this, run a query to get all the current statuses that are on hold and the on-hold end date is less than the current date (AKA we want to switch them back). Then it loops over the query and inserts a new history row for each user that needs status updating.
You run a lot of queries in the first method. There is a better (or at least different) way:
<cfquery>
insert into userstatushistory (UserID, StatusID, StatusDate, endDate)
select UserStatusHistory.userID, UserStatusHistory.StatusID, now(), null
from UserStatusHistory
where UserStatusHistory.StatusID = [on hold ID] and
userstatushistory.userstatushistoryID in (select top 1 ushsub.userstatushistoryID
from userstatushistory ushsub
where ushsub.userID = userstatushistory.userID
order by statusdate desc
) and
UserStatusHistory.enddate <= GETDATE()
</cfquery>
All done within a single cfquery statement. There is no looping and no multiple queries. The query runs in one fowl swoop, theoretically without bogging down your server.
This post was not proof-read, and the code may have typos.
There are no comments for this entry.
[Add Comment] [Subscribe to Comments]