Skip navigation
Currently Being Moderated

update query

Oct 18, 2013 7:14 AM

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.

 
Replies
  • Currently Being Moderated
    Oct 18, 2013 11:06 AM   in reply to jfb00

    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.

     
    |
    Mark as:

More Like This

  • Retrieving data ...

Bookmarked By (0)

Answers + Points = Status

  • 10 points awarded for Correct Answers
  • 5 points awarded for Helpful Answers
  • 10,000+ points
  • 1,001-10,000 points
  • 501-1,000 points
  • 5-500 points