Delete multiple tables

Anonymous — Wed, 13 Feb 2019 13:18:37 GMT

Hello Talend Community,

I'm an absolute beginner in Talend. I have to create a job to have to:

Task1. Delete some rows from tableNameA which correspond to certain conditions, more precisely Status1=xy and Status2 = tz. This is actually quiet simple since these conditions are included into the WHERE clause in the query:

DELETE FROM TableNameA

WHERE Status1 = xy AND Status2 = tz

The query above will delete all the rows that matches the WHERE clause, including values of a column named DeliveryId.

Task2. Drop multiple tables, said tableNameB, that contain the same DeliveryId column as tableNameA. Note that tables B are named according to the following format: "tableNameB_xyz", where xyz is the DeliveryId.

In my job I use a tFlowToIterate component linked to a tMySqlRow component in order to perform task 1, as can be seen in the attached figure.

My question is, how can I drop all the tables "tableNameB_xyz" that match the DeliveryId selected in Task1?

Thanks in advance

Best Regards,

Re: Delete multiple tables

vapukov — Wed, 13 Feb 2019 20:54:19 GMT

Hi,

it is not about Talend, it about database

you need add 1 more iterate level for each parent iteration

use tMySQLInput for request all tables with matched name from information_schema

https://dev.mysql.com/doc/refman/8.0/en/tables-table.html

select TABLE_NAME from information_schema.TABLES where TABLE_NAME LIKE 'tabel_pattern'+XXX

iterate thru resultset and use data for prepare delete statements

topic Delete multiple tables in Talend Studio

Delete multiple tables

Re: Delete multiple tables