Getting IDs to update data in the database

I have three tables, one master table. The Id of that master table is used in 2nd table which provides us with a records of multiple IDs against that particular master Id. Again the multiple IDs of 2nd table, will provide us another set of records in the 3rd table with multiple IDs against each IDs of 2nd table. So, now I want to update the records of 2nd and 3rd table. Can anyone please suggest me how to do it. 

For eg: the Id of the 1st table is 1 i.e Master Id

2nd table looks like

Id.    Master I'd.    Name

1.             1.                 Abc

2.             1.                 Def

3.              1.                Ghi

3rd table looks like

Id.        Id of 2nd table.      Roll no.    StreetNo

1.                   1.                         45.             6

2.                   1.                         55.             3

3.                   2.                         2.               65

4.                   2.                         7.                7

5.                  3.                          78.              2

6.                  3.                          3.                76


So, now if I want to update the name, roll no and street number, how should I do it.  The data will be entered to the database through form. So, the updated data should be visible in the form. 

Can anyone please help me. I am also facing problem getting the set of IDs from the 2nd and 3rd table. I tried applying filter on the 2nd and 3rd table using the master id for that, but it is giving me the Id of the 2nd table as 0. 

For update, I am using for each loop.

However, I could not get the desired result. I am doing it in reactive Thank you.

You will need to loop on the second table all the ids that are equal to the master id you want to update also, you will need to get the Id. of the second table to update the specific row.

On the third table, you will need to get the   Id. of the 2nd table and the Id. of the third table.

Let me know if you need more help.

But you will need any ids to match the record you want to update.

To help you more the best thing is to share an OML.

Regards,

Márcio

mvp_badge
MVP

To me the solution seem to look way more performant if SQL would be used to update the records for this scenario.

Hi Runisha,

I have done similar thing in one of the project and I found the best solution was to store data in a structure first. So you have to create a structure which will have all these three entities inside in, in this way you can store all data in single structure and insert or update in any of the table in one go. The structure will look like this : 

Use DataFetch action and take output parameter as above structure type, bind all screen data with this Datafetch output.

Another way will be using Advance SQL tool where you have to perform join between all three tables. Query will be like this :

Update T3

Set T3.Roll = 123 , T3.Street = 'Abc'

From table3 T3

Join table2 12 ON t3.Id = t2.id

Join table1 T1 ON T2.id = T1.id

Where T3.Roll = 45 and T2.Id = 1


Hope it helps!

Sanjay

mvp_badge
MVP

Regarding filling a structure; what if the combined number of records goes into the thousands, then loading this all in memory can be become an issue especially if this is executed by multiple users at the same time.

Community GuidelinesBe kind and respectful, give credit to the original source of content, and search for duplicates before posting.