cfloop

[Guest]

Hi i need to loop through a query then set the MesID each time i loop through, just want to make sure this code will work!! i need it to delete the current record at the end of the loop

<!-- GET INFO FROM MESSAGE TABLE-->
<cfquery datasource="#application.ds#" name="GetMessages">

<!-- LOOP THROUGH MESSAGE TABLE-->
<cfloop query="GetMessages">
<cfset CurrentID = GetMessages.MesID>

<!-- INSERT INTO TABLE HERE-->

<!-- THEN DELETE CURRENT MESSAGE HERE-->
<cfquery datasource="#application.ds#" name="GetMessages">
DELETE FROM Message_Table
WHERE MesID = #CurrentID#
</cfquery>

</loop>

[Dan_Bracuk]

You can probably do it without a loop, but without seeing the initial and insert queries, it's hard to say.

[Guest]

Hmmm... deleting a row while your pointers are actively looking at it? That's an enqueing conflict. In essence you're attempting to delete [read: write on] your row while you have it locked in "share" mode. It depends upon your database... Some will simply change your privileges to "write" and complete the update; others will give you a stream of warning messages, but perform the update anyway, and still others will stop the update altogether.
[Basically it's NOT a good habit to get into!]

[akuo]

I'm assuming you are trying to copy from 1 table to another...and then deleting from the orginal. If so, try this...(only 1 delete vs. many deletes)

(Keep in mind, you should use <CFTRANSACTION> in case an insert or delete fails midway).