"How to update rows in database table?"

alaserm

Hi everybody,
I am newbie to RapidMiner and I would greatly appreciate if anybody can help me with following problem

After I run classification model I get example set as output with classification label, now I need to go back and update label column in sql server database for each row in exampleset, I tried to use 'Execute SQL' operator, but I don't know how to iterate thru exampleset inside this operator, is that the right operator to use or is there built-in operator that can update row in sql table?

dan_agape

One of the easiest ways (and seemingly efficient in terms of database access) may be based on writing the scored data in a new table (with Write Database operator), and then dropping your original table if you wish (with Execute SQL operator including the query DROP TABLE originaltable) and keeping the new table.

Alternatively, if you wish to really update your original table, you can just save the id and the crisp prediction columns from your scored dataset in a new table (via Write Database operator). Then run 3 SQL commands:

ALTER the original table by ADDing a new COLUMN called score for instance

UPDATE the original table by SETting the column score to the value obtained from a subquery (SELECT prediction FROM newtable WHERE newtable.id=originaltable.id)

DROP TABLE newtable

Obviously id needs to be a column that uniquely identifies the rows in your dataset.

Regards
Dan