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

Classic Servoy
1

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…

2

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.

3

Hi Marc,

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

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.

4

Thnkx Robert, I guess this can help me.

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