24
Views
8
Comments
Update records in one table with value from another
Question
Service Studio Version
11.10.7 (Build 37468)

I hope this simplified version of an earlier question is sufficiently clear. And please pardon me for the prior one I posted a few days ago.

I'm new to Outsystems and not a developer by history, but I have completed all Becoming a Mobile Developer Guided Path Lessons.  I just haven't been able to figure out how to accomplish the following:

We have two tables: A and B

Both tables have a Date field and an Int field. In some cases, the dates in each table will match.

The objective is to: Iterate through the list of sequentially-dated A records , and update the Int field in records from table B where the dates in A and B match, using the A record's Int value.

I've attached a simple implementation of what I'm trying to accomplish, and would certainly appreciate some guidance on how to accomplish the objective.

ConditionalUpdate.oml

Rank: #686

Hi Matt,

you can do as below to perform your use case. Also attaching oml for better understanding


Cheers,

Dileep

ConditionalUpdate.oml

Rank: #5686

Thank you, Dileep. I appreciate that you referenced one query ("Aggregate") within the other, eliminating need for the local variable -- cleaner code is good.

I reviewed the oml and am experiencing the same "Record to update does not exist..." error I've seen using a variety of methods/attempts. It appears the UpdateLocalB query ("Accelerator") is not aware of the record that - in fact - meets the Filter condition.  

Let me know if you come up with a solution. And thanks again for your time.

Champion
Rank: #315

I think you dont need to iterate every record as if you have huge recordbase then it will impact performance.

you can do it with advance sql query.

please go through below link:

https://stackoverflow.com/questions/224732/sql-update-from-one-table-to-another-based-on-a-id-match

if you not able to understand I can write a query for you.

Rank: #686

Hi Abdul, did I missed something , Is there any possibilities to write advanced query on local database entity??

Champion
Rank: #315

No, You are right, Actually I missed that its Local entity.  

Champion
Rank: #315

Something like this :

UPDATE

    A

SET

    A.IntField = B.IntField

FROM

    A 

INNER JOIN

    B 

ON 


    A.DateField = B.DateField;

Rank: #5686

That's right, Dileep and Abdul. Unfortunately, there's no option for using SQL queries on local tables.  I will keep trying to find a solution that avoids making repetitive calls to the server, by achieving the objective using local tables...and then synchronizing the data once after all records have been processed.

Rank: #5686

Thanks Dileep. Your suggested implementation works perfectly - when implemented in a server action, against server database tables i.e., not local tables. And Abdul, your suggestion helped me think about this from the server point of view, ultimately leading to finding the solution.  I appreciate you both helping me sort this out.