🦉 🎤   RapidMiner Wisdom 2020 - CALL FOR SPEAKERS   🦉 🎤

We are inviting all community members to submit proposals to speak at Wisdom 2020 in Boston.


Whether it's a cool RapidMiner trick or a use case implementation, we want to see what you have.
Form link is below and deadline for submissions is November 15. See you in Boston!

CLICK HERE TO GO TO ENTRY FORM

"How to update rows in database table?"

alasermalaserm Member Posts: 1 Contributor I
edited May 23 in Help
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?
Tagged:

Answers

  • dan_agapedan_agape Member Posts: 106  Guru
    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
Sign In or Register to comment.