Hi @natasha, I think you are on the right track but you don't need to create a list of the related records in the other tables. If you can collect values of the key field of all the records which should be deleted from the master table, you can delete all the related records from other table(s) using a SQLExecutor beforehand.
The SQL DELETE statement can be created dynamically using the feature attributes in the workflow. That is, for example, it's possible to set something like this.
delete from @Value(tableName) where @Value(fieldName) = @Value(value)
Hi @natasha, I think you are on the right track but you don't need to create a list of the related records in the other tables. If you can collect values of the key field of all the records which should be deleted from the master table, you can delete all the related records from other table(s) using a SQLExecutor beforehand.
The SQL DELETE statement can be created dynamically using the feature attributes in the workflow. That is, for example, it's possible to set something like this.
delete from @Value(tableName) where @Value(fieldName) = @Value(value)
Thanks @takashi. In order to do this, would I put the SQLExecutor after the DELETE AttributeCreator and connect the Output port of the AttributeCreator to the Initiator port of the SQLExecutor? Also, when I use the SQL Statement text editor and choose the tables and fields from the menu on the left, it populates the syntax with this format:
delete from "tours"."tour_url" where "tour_id" ="id"
Does that seem right or should the syntax be more consistent with your example?
("tours.tour_url" is one of the other tables holding the associated records. "tour_id" is the column in "tours.tour_url" that links to the "id" field in the master table).
Thanks again @takashi!
Hi @natasha, I think you are on the right track but you don't need to create a list of the related records in the other tables. If you can collect values of the key field of all the records which should be deleted from the master table, you can delete all the related records from other table(s) using a SQLExecutor beforehand.
The SQL DELETE statement can be created dynamically using the feature attributes in the workflow. That is, for example, it's possible to set something like this.
delete from @Value(tableName) where @Value(fieldName) = @Value(value)
Yes, I think the SQLExecutor works as expected if you insert it between the AttributeCreator-DELETE and the writer feature type. Connect between AttributeCreator | Output and SQLExecutor | Initiator, SQLExecutor | Initiator and the writer feature type.
For example, this SQL statement would be possible if the features coming from the AttributeCreator have an attribute called "tour_id" that stores the id of a record to be deleted. Assuming the id value is a character string.
delete from tours.tour_url where tour_id = '@Value(tour_id)'
And,if the destination database has two or more related tables from each of which a corresponding record should be deleted, this setting is also possible (using semicolon as delimiter between statements).
FME_SQL_DELIMITER ;
delete from tours.tour_url where tour_id = '@Value(tour_id)';
delete from tours.tour_foo where tour_id = '@Value(tour_id)';
delete from tours.tour_bar where tour_id = '@Value(tour_id)';
Note that the rules regarding quotation marks for table/field names depend on the destination database format.
Awesome, thank you @takashi. That looks like it worked! You are a genius!
