Delete rows from table where data matches input data

[Anonymous]

I have a CSV file that I load into my MySQL database. The file contains various data (always the same kind of course), but also month and year. I would like to delete all data in my destination table that has the same month and year as my CSV file contains. Right now I go in to mysql workbench and type "Delete from table XXXXX where fiscal_year = 2014 and month =1" - but how can I incorporate it into my load in TOS instead?

[janhess]

Use a tMySQLROW to run the query before loading the new data.

[Anonymous]

Thanks for your reply. That is a good option - but that requires me to run the query and change the parameters manually each time - as I do it today in MySQL Workbench? It is a step forward as I can do this in Talend only. But I would prefer if there were some kind of option during my ETL process where I could dynamically read the month and year and then delete the data from my destination table if they are there?

[Anonymous]

Hi,
It seems your are looking for a dynamic sql query with Context or Variable Value in query? Did you check the component TalendHelpCenter:tMysqlTableList.
Best regards
Sabrina

[Anonymous]

I have posted my flow - the destination "Ledger_new" contains all data including perhaps current month and year. The input "Ledger" contains new data, but some of them could already be in the "Ledger_new" destination. To be sure I would therefore like to run a command where the fiscal_year and month from input "Ledger" delete values in output "Ledger_new" whether they are there or not. I have tried tMySQLRow where I can delete the rows I want, but only by typing in manually which fiscal_year and month I would like to delete. How can I do this dynamically? I have tried to look at tMySQLTableList, but I am unsure how to use it in this regards?

[Anonymous]

Deleting rows in a table is quite simple.
Use a tMysqlOutput component and give it a flow which contains all columns you need to identify the rows to delete. Set all columns in this schema as key column and configure the tMysqlOutput in the Action on data to Delete.
For the table I would use a context variable. I guess you have to delete a couple of rows in some of your tables.
I would create a job which deletes the rows for exactly one table. But the table name is not fixed set instead the table name is a context variable and this variable have to be set from a surrounding job or within the job within a loop over your tables to process.

[_AnonymousUser]

Deleting rows in a table is quite simple.
Use a tMysqlOutput component and give it a flow which contains all columns you need to identify the rows to delete. Set all columns in this schema as key column and configure the tMysqlOutput in the Action on data to Delete.
For the table I would use a context variable. I guess you have to delete a couple of rows in some of your tables.
I would create a job which deletes the rows for exactly one table. But the table name is not fixed set instead the table name is a context variable and this variable have to be set from a surrounding job or within the job within a loop over your tables to process.

Hi,
it would be nice solution but in that case key field for deletion must be set (at least one key) to delete all preselected rows. It will be complicated to get it since schemas are not updated for tables in loop and key in field_options cannot be set dynamically and primary keys are not fetched from database (tested on postgres) and must be set manually in schema editor.