Supprimer les données en base de données (Mysql) qui n'existent pas dans les fichiers sources

[TDIALLO]

Bonjour,

Je manque d'idée pour supprimer des données en base qui n'existent plus dans les fichiers sources.

En effet je charge les données de plusieurs fichiers dans une table en base de données(Mysql) par itération et je veux supprimer les références qui sont en base mais qui ne sont plus dans les fichiers (Un fichier par référence).

Comment pourrait-je comparer ce que j'ai en base et ce que j'ai dans les fichiers en les chargeant un à un et puis supprimer ceux qui sont en base qui n'existent plus dans les fichiers

Merci pour vos retour

Cdt

Tdiallo

Hello,

I'm running out of ideas to delete database data that no longer exists in the source files.

Indeed I load the data of several files in a table in database (Mysql) by iteration and I want to delete the references which are in base but which are not any more in the files (one file par reference).

How could I compare what I have in the database and what I have in the files by loading them one by one and then delete those in the database that no longer exist in the files

Thank you for your feedback

Tdiallo

[Anonymous · Accepted solution]

You need to identify a key or a set of keys which are common between your data. By that I mean, you need to find values in your database table which exist in your file. Once you have those, then it is just a case of using a tMap to compare your DB table against your files and removing the data that does not match.

Maybe you can show us where you are with this job (a screenshot) and we can help you move forward on it.

[TDIALLO]

rhall,

Tank you for jour feedback

This is kind of what I want to do but here I'm iterating knowing that I have 44 files and each file only contains one reference (key). how do I delete what I have in the database that I don't have in the files?

 

[Anonymous]

OK, I suspected that this might be the issue. What you can do is use a tFileList to iterate over the files that you need to check, gather the data for each and store it in memory (maybe a tHash or tHSQLDB component). Once you have it in memory, you can read use the complete set of file data in one go with the tMap. This way you will know that any missing data is actually missing from all of the files.

[TDIALLO]

hello rhall,

Thank you for the answer. I thought about it but the problem is that I have other sub-jobs(6) that must be processed before loading the next file

[Anonymous]

Why do they need to be processed before loading the next file? Do the subjobs do anything to/with the files that will change the file contents or the database table contents? If not, then you should be able to do this.

From your screenshot I see that you are iterating over the whole job from the tFileList. What I meant, when I was describing the solution, was to have a separate subjob at the beginning of the job. Load all of the file data into memory and keep it there until you need it. You can do whatever you want between loading the data into memory and using it to check your database table.

[TDIALLO]

rhall,

Thanks for your return,

I wanted to do it in the sub job, but hey if there is no other solution, I have to do it like this.

Thank you for this suggested solution.

[Anonymous]

You can do it anywhere in the job, but you must check all of the data in all of the files against the DB table at the same time. Otherwise data that is in file 5 will be removed from the DB table when only file 1 is checked in the first iteration of checks.

You could do it in a slightly different way. You could check each of the files in the way you currently are, then collect the matched keys in memory and do nothing with the db other than read it. Then at the end of the job, use the matched list to filter out the records in the DB that are not matched. That will also work.

[TDIALLO]

hi rhall,

Finally I opted for the first solution and it does the job well thank you very much.

[Anonymous]

Not a problem. Glad it worked 😉