update query

[jfb00]

Hi All,

I have this query in CF9:

//create an empty query to work with
variables.qryFoo = queryNew("myID","DOUBLE");
//add a row and fill it with some data
queryAddRow(qryFoo);
querySetCell(qryFoo,"myID","1");
queryAddRow(qryFoo);
querySetCell(qryFoo,"myID","2");

Here is my update statement:

<cfset myIDs = valueList( qryFoo.myID ) />
<cfquery datasource="#application.str_dsn#">
    update myOtherTable
     set trueValue = 1
     where myID in ( #myIDs# )
</cfquery>

It works fine until it reaches more than 1000 ids. I am getting this error:

ORA-01795: maximum number of expressions in a list is 1000

I found this statement:
"You cannot have more than 1000 literals in an IN clause. You can, however, have SELECT statements in your IN clause which can return an unlimited number of elements i.e."

I try to do an update inside of Q of Q but i cannot do it:

There was an error processing the update. <br><b>Query Of Queries syntax error.</b><br>

Encountered "update.

Error Executing Database Query.

Anyone has an ideas who to perform this update for more than 1000 ids?

Thanks in advanced.

[BreakawayPaul]

Try this?

http://stackoverflow.com/questions/400255/how-to-put-more-than-1000-values-into-an-oracle-in-clause

[jfb00]

Thanks for your reply.

Can i use temporary tables in CF9? I never use this before.

I try this:

          <cfquery name="tempTable" datasource="#application.str_dsn#">

                create table #tempIDS (myID int);

                insert into #tempIDS (myID)

                    values (4);

            </cfquery>

I am getting error "Invalid CFML construct"

Thanks

[REEDPOWELL]

The invalide construct error is because of the # in the temp table syntax, so you'll need to double that up.

The problem with your attempt to mix it with QofQ is that you cannot mix a db query and a QofQ query - they get processed by totally different systems.  You are going to need to take the temp table route, so that you can replace the list of IDs in the WHERE clause of the SELECT statement with a subquery that gets the IDs out of the temp table.  If using a #temp table does work then you will need to create an actual table and then drop it when you're done.

[jfb00]

Thanks for you reply and help.

I try the double ## and now I am getting error: ORA-00911: invalid character.

I cannot use real table because my online user doesn't have create or delete permissions.

Anyone use temp tables in CF9 and oracle 11g?

Best,