Use foreign keys to update/ delete matching records in multiple tables

[teal]

Hi,

I'm struggling with deleting and updating multiple tables from the same input data.

Here is a screenshot of the job.

I'm comparing the rm table in the v24 MSSQL db and in the v26, if a record was deleted in the v24, it has to be deleted in the v26. This is what's happening with tMap_1 and it works. It finds the matching bl_id, fl_id and rm_id as keys.

But before deleting that record in the rm table, the three keys are used as foreign keys in other tables records and have to be deleted or updated.

In this case, I create a second row from tMap_1 which holds the bl_id, fl_id, rm_id that have to be adapted. I link it to a first table 'rmpct' and use a delete, it seems to work even though it's primary key is not used (I adapted the field options for the deletion keys).

But now what I'm trying to do is in the tables that have matching keys, I need to update the values of these keys in the record (they have to become empty). I tried it with another table here "activity_log" but it doesn't find the matching rows based on the foreign keys and I don't know how to update these values with different values than the input row.

Can somebody help me please?

Thanks!

[Anonymous]

Have you checked the values that are getting passed to the final tMap? To do this, you can simply add a tLogRow between the rmpct and the tMap_2. This will show you the values that are being sent. Then you need to check that these are being matched by the activity_log values. Simple pick some values that are being shown in the tLogRow and check those against the values that the activity_log table holds. Make sure that the join in the tMap is carried out correctly. If the values are as you expect, but it isn't working, then we will need to see the tMap configuration.

[teal]

Thanks for your answer!

So what comes out of rmpct are the right values after checking it with a tLogRow, but when I insert them into the tMap_2 with activity_log it doesn't find matches. I checked with SSMS and these keys exist. Any idea? Here is the configuration of tMap_2.

[Anonymous]

OK, so your tMap looks fine. So I'd look at the data. You have an inner join on 3 values (bl_id, fl_id rm_id). Are you sure that there are rows with all of those values present in the same row for each row that comes from rmpct? Also, I see that these ids are Strings. Be sure that there are no leading or trailing spaces in these values which could be causing the values to be be different.

Another thing to try here is to add a tLogRow between tMap_2 and the activity_log update component. This will test the above (if you get rows, you have answered the questions above). But it will also identify whether this could be some sort of DB locking issue. I have just noticed that you are reading from the same table you are intending to update. This *shouldn't* be a problem, but can in some situations cause issues. Having said that, your activity_log lookup will have been read completely by the time the update triggers, so this shouldn't be the cause. But these are the things I would be looking for while trying to figure out what is happening.

[teal]

Sorry for my late reply.

It was indeed a problem due to trailing spaces..

I wanted to replicate the update of the three foreign keys by nulls in the different tables necessary by using tReplicate as you can see hereunder.

The problem is that it has to do so in 6 tables which takes way too long (30 min). Any idea on how I could optimise this?