Looping inside a query

Hello All,

Can anyone tell me why this won't work. I think it has something to do with evaluate, but that wasn't working either.

I have the following code

<cfquery name="ReportList" datasource="#variables.datasource#">
BEGIN
<cfloop index="i" from="1" to="2">
UPDATE REPORTS SET FILTER_OPTIONS=0 where report_id=145;
</cfloop>
end;
</cfquery>

I know the SQL is isn't logical, but it is valid (I can copy the SQL from the error page right into SQL Plus and it will run fine). here is my problem though, when i try to run this code i get an error saying it has run into double quotes when expecting something else. i have tried wrapping the SQL in evaulate, preservesinglequotes, and anything else i thought might remove the error, but all to no avail.

What's the syntax on the error message?

Error Executing Database Query.
[Macromedia][Oracle JDBC Driver][Oracle]ORA-06550: line 1, column 7: PLS-00103: Encountered the symbol "" when expecting one of the following: begin case declare exit for goto if loop mod null pragma raise return select update while with << close current delete fetch lock insert open rollback savepoint set sql execute commit forall merge pipe

I'd just do them in separate cfquery statements:

<cfloop index="i" from="1" to="2">
<cfquery name="ReportList" datasource="#variables.datasource#">
UPDATE REPORTS SET FILTER_OPTIONS=0 where report_id=145;
</cfquery>
</cfloop>

The SQL statement is just a valid one for my testing, what i want to be able to do is loop thru a couple hundred queries using the same Cfquery tag (makes it more effiecent). I have seen other sites that have done this, but i don't know what kind of back end they were using.

You can also try something like this:

<cfquery name="ReportList" datasource="#variables.datasource#">
<cfloop index="i" from="1" to="2">
UPDATE REPORTS SET FILTER_OPTIONS=0 where report_id=145;
</cfloop>
</cfquery>

But if you're looking for efficiency, why not do all the updates in a single statement? Like

<cfquery name="ReportList" datasource="#variables.datasource#">
UPDATE REPORTS SET FILTER_OPTIONS=0 where report_id in (#valueList( reportList.report_id )#)
</cfquery>

I tried that, same issue as before.

I would make it one query if i could, but I want this for things other than updates (like a mass insert) I was just using an update startment for my test cause i wanted to limit any bad data I would create in my database (who needs extra records?).

Yep, I'd say if you don't want to do it as a single statement that you'll have to do it as separate cfquery statements. The JDBC driver may be applying additional formatting or ending statements with the semicolon automatically.