How to update a foundset (1 of more columns) most efficient?

[mboegem]

Can anyone tell me the most efficient way to update a foundset for 1 or more columns?

With a fixed value this is easy and fast via databaseManager foundsetupdater.

What I have here is:
2 tables, connected to eachother via primary & foreign key
'foreigntable' has: column X
and I need to get the related value as fast as possible into a large foundset in the 'primarytable'

I could do this by iterating the foundset on a form, but this will be slow.

Is there any way to do this via the foundsetupdater as well, or to push a dataset back to the table (matching key of course)?

Maybe I just overlook possibilities, but I can't get it clear at the moment...

[agiletortoise]

I'm not sure what your criteria are...but I suspect you could use several passes with foundsetupdater base on different queries rather than looping over the foundset.

g.

[ROCLASI]

Hi Marc,

Maybe something like this ? Let the database server do all the work for you:

Code: Select all

var sQuery = "UPDATE myParentTable a SET a.myParentColumn = (SELECT b.myChildColumn FROM myChildTable b WHERE b.parentIdColumn=a.myParentColumn)";
if ( plugins.rawSQL.executeSQL("myServer","myParentTable", sQuery) ) {
   // make sure all clients see the changed data.
   plugins.rawSQL.flushAllClientsCache("myServer", "myParentTable");
} else {
   // an error occured
}

Hope this helps.

[mboegem]

Thnkx Robert, I guess this can help me.

I was hoping for a possibility with Servoy commands, but this will do the job...