Finance | Project Operations, Human Resources, ...

How to update multiple records in an efficient way?

(0) Share

Report

Posted on by junior AX

1,552

Hi All,

I have a container that has multiple salesId and i added a field to the salesTable let's say called booleanFlag.

i want to update all records in salesTable where the booleanFlag = false and where salesId is not equal to sales Ids in the container to booleanFlag = true

so let's say container has these salesId = 1,2 and 3

i want sth similar like this but instead i want to put values of container and i want to be efficient cause there might be alot of records.

update_recordset salesTable
setting booleanFlag= true
where salesTable.BooleanFalg == false
&& (salesTable.SaledId = 1 
|| and salesTable.SaledId = 2
|| and salesTable.SaledId = 3);

Would this be considered effecient?

for (int i = 1; i <= conLen(cont); i  )
            {
                ttsbegin;
                SalesTable  salesTable;
                update_recordset  salesTable
                    setting booleanFlag = false
                   where salesInvoice.SalesId == conPeek(cont, i)
                   && booleanFlag != false;
                ttscommit;
            }

I have the same question (0)

All responses (4)

Answers (0)

Suggested answer

nmaenpaa 101,160 Moderator on at

Like (0)

Report

You can use the SysDa framework to create dynamic select / update statements:

docs.microsoft.com/.../sysda

It uses update_recordset for update operations.

Was this reply helpful? Yes No
Blue Wang on at

Like (0)

Report

HI JuniorAX，

Just from your code:

You used conpeek() in where statement .

I think you should use the variable there.

Was this reply helpful? Yes No
Suggested answer

Martin Dráb 237,965 Most Valuable Professional on at

Like (0)

Report

If you have a lot of sales IDs to consider, you could also put them into a TempDB table and not-exist join it with SalesTable. But it has certain overhead; it wouldn't be efficient for small amounts of data.

You should also ask yourself why you have sales IDs in a container in the first place. If you get them by a query, maybe you should use the query here. If you get them from users, maybe you should put them to TempDB table straight away and no storing them in memory (in the container) at all.

By the way, your code uses "equal SalesId" while you description says that you want "not equal".

Was this reply helpful? Yes No
Suggested answer

Sergei Minozhenko 23,093 on at

Like (1)

Report

Hi JuniorAx,

One more comment about set-based operations is that you need to disable data methods, events and database log, otherwise update_recordset will be equal to while select + update.

docs.microsoft.com/.../maintain-fast-sql-operations

Was this reply helpful? Yes No