Question

Deleting records from a relational database

  • 29 June 2017
  • 4 replies
  • 30 views
N
Badge

Hi everyone,

I having trouble working with a Postgres relational database. Basically what's happening is I'm reading from one (or many) XMLs files, transforming/combining/editing data then writing to a number of different Postgres tables which all link together via a primary key in the 'master' table. When the XML file(s) get updated and a record drops off, I need to delete that record from the master table but because of the related records in the other tables, I am unable to do so. Obviously the related records in the other tables need to be deleted as well (and apparently before I can delete the 'master' record).

Does anyone have some logic around how to make this work? It seems I need to find the deleted 'master' records (I'm using Update Detector at the moment), then somehow get a list of all of the related records in the other tables, delete them, then delete the 'master' record. Any tips or advice would be greatly appreciated!

Natasha

 

4 replies

takashi
Rank
Userlevel 2
Badge +17

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)

N
Badge

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!

 

0684Q00000ArMWiQAN.jpg

takashi
Rank
Userlevel 2
Badge +17

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.

 

N
Badge

Awesome, thank you @takashi. That looks like it worked! You are a genius!

Reply


Community Stats

28,731
Posts
109,528
Replies
36,565
Members
Terms & ConditionsCookie settings
A product of
Safe Software respectfully acknowledges that we live, learn and work on the traditional and unceded territories of the Kwantlen, Katzie, and Semiahmoo First Nations.