Options

[Solved] How to delete records from a database table

MacPhotoBikerMacPhotoBiker Member Posts: 60 Contributor II
edited November 2018 in Help
Hi,

is there a way to delete records in a database table based on the "Filter Example" operator?

Let's say I want to filter all records where year = 2013. I can easily create the filter, but I found no way how to physically delete the records in the database.

Thanks for any hint!

Answers

  • Options
    Marco_BoeckMarco_Boeck Administrator, Moderator, Employee, Member, University Professor Posts: 1,993 RM Engineering
    Hi,

    As there is no direct "Delete" operator for databases yet, I would suggest to use a combination of a filter to get an example set of entries you want to delete, then use a "Loop Values" (if you only need to delete entries matching one column) or "Loop Examples" operator and place an "Execute SQL" operator inside the loop. The query you define could look like this: 

    DELETE FROM table_name WHERE column = '%{loop_value}';
    Regards,
    Marco
  • Options
    MacPhotoBikerMacPhotoBiker Member Posts: 60 Contributor II
    Hi Marco,

    thank you very much!
  • Options
    MacPhotoBikerMacPhotoBiker Member Posts: 60 Contributor II
    In my particular case, there's not much risk. I'm getting every week an Excel table that contains invoicing details of the entire year. Before importing the new data, I simply want to delete the existing values in the table, and the import the entire year from Excel.

    So, for me that works. But I guess that's why they say "With freedom/ power comes responsibility" ;)
  • Options
    BalazsBaranyBalazsBarany Administrator, Moderator, Employee, RapidMiner Certified Analyst, RapidMiner Certified Expert Posts: 955 Unicorn
    SQL injection can be mitigated by using the "prepare statement" option. The SQL exploit from the legendary XKCD comic wouldn't work if you use "prepare statement" and enter the macro value as a parameter.
Sign In or Register to comment.