[Solved] How to delete records from a database table

MacPhotoBiker

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!

Marco_Boeck

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

MacPhotoBiker

Hi Marco,

thank you very much!

MacPhotoBiker

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"

BalazsBarany

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.