Unanswered

use "delete from" to delete records

(1) Share

Report

Posted on by CU22120935-0

Hi,

Can i get rid of the while loop in the code below and make the delete faster?

I have an order header table (table1), where this order has 2 types of lines (tableline1 and tableline2)
so it's like salesTable and imagine there is salesLine1 table and SalesLine2 table both linked to header salesTable

and i want to delete all orders with their 2 table lines where the header has a specifIc value for InventLocationId
and of course i need to make sure i delete the lines tables first before the header

class DeleteOrderService extends SysOperationServiceBase
{

    public void deleteOrder()
    {
        Table1              table1;
        TableLine1          line1Delete;
        TableLine2          line2Delete;
        Table1              table1Delete;


        while select Id from table1 where table1.InventLocationId == XParameters::find().LocationId
        {
            delete_from line1Delete where line1Delete.Id == table1.Id;
            delete_from line2Delete where line2Delete.Id == table1.Id;
            delete_from table1Delete  where table1Delete.Id == table1.Id;
        }

    }

}

it's like i would like to delete everything that this query returns:

select TABLE1.ID,INVENTLOCATIONID,*from TABLE1
join TABLELINE1 on TABLELINE1.ID = TABLE1.ID
join TABLELINE2 on TABLELINE2.ID = TABLE1.ID
where INVENTLOCATIONID = 'XX'

I have the same question (0)

All responses (6)

Answers (0)

André Arnaud de Cal... 301,194 Super User 2025 Season 2 on at

Like (0)

Report
Hi,

You can add a table join when using the delete_from command, an example:

delete_from line1Delete exists join table1 where table1.Id == line1Delete.Id && table1.InventLocationId == XParameters::find().LocationId;

Was this reply helpful? Yes No
Martin Dráb 237,987 Most Valuable Professional on at

Like (0)

Report

You need such code only when your tables aren't designed correctly. Because order lines don't make sense without any order header, when an order is deleted, lines must be deleted too. The system will do it automatically for you, you just need to set On Delete property on the relation from the line table to the header table. The value should be Cascade.

Was this reply helpful? Yes No
André Arnaud de Cal... 301,194 Super User 2025 Season 2 on at

Like (0)

Report

Hi,

In case you have set delete actions to cascade, related lines will be deleted when you delete the records from table1. Note that in this case, the record based deletion will be converted to a row-by-row delete action.

An inner join doesn't make sense in the delete_from statement as it will delete the records from the first mentioned table only. Performance wise, your three delete_from statements you mentioned in your reply might be the fastest option.

Was this reply helpful? Yes No
Martin Dráb 237,987 Most Valuable Professional on at

Like (0)

Report

Note that your code will still call delete() method and execute delete actions (to check if there aren't any related records to delete) unless you suppress that by methods like skipDeleteActions(). But you must be 100% sure that there is no important logic in delete() methods or any event handler, that all delete actions can be ignored and so on, otherwise your code will cause data corruption. While there are cases when you can make such assumptions, it's very dangerous in general.

Was this reply helpful? Yes No
Martin Dráb 237,987 Most Valuable Professional on at

Like (0)

Report

1. I'm talking about any delete_from statement, which includes yours and André's code too.

2. Which three do you mean? Also note that looking at code alone won't tell you whether the execution will run row-by-row because it depends on other things too (e.g. whether there are delete actions, DB log etc.).

3. Yes, deletion will run row-by-row, even if you use delete_from (unless you call a skip* method, as I mentioned).

4. I can't answer that. I know nothing about rentDeviceLine, for example. But I would assume that the code was put there because deleting the related rentDeviceLine records is necessary (and not calling it would conflicting with that).

5. If you want to invest your time to it, analyze all related tables, data methods, event handlers, business events, logging requirements etc. to see whether you really can skip everything and make a bulk delete. Maybe you'll find that you can't do that. If you find that it's possible, you must consider whether you really want to do it. You're comparing performance gains with risks regarding business logic. For example, if you skip data methods, there is a risk that code will be added there later without taking into account that it won't be called in some cases.
If you don't want to do such as analysis, don't skip any business logic.

Was this reply helpful? Yes No
Martin Dráb 237,987 Most Valuable Professional on at

Like (0)

Report

2. It depends on whether delete_from will delete records one by one, which - as I mentioned - depends on other things than just your code, therefore the question can't be answered.

3. Your original code will be the least efficient because you load every header record twice. If there is a delete action on header (and you don't skip it), deleting lines separately would make things less efficient because the header would still check if there aren't lines to delete by the delete action.

4. Assuming that the code has a reason for existence, simply ignoring it would be a bug. You can skip this call if you do the deletion somewhere else.

5. You can't say for sure that no DB logging is and never will be enabled in any environment. That it's not at the moment isn't sufficient. Also, it's about many more things than just logging (as I already explained there).

Was this reply helpful? Yes No

Join the conversation

Community site session details

use "delete from" to delete records

Helpful resources

Quick Links

November Spotlight Star - Khushbu Rajvi

Forum Structure Changes Coming on 11/8!

Dynamics 365 Community Platform update – Oct 28

Leaderboard > Finance

Featured topics

Product updates

Community site session details

use "delete from" to delete records

Helpful resources

Quick Links

November Spotlight Star - Khushbu Rajvi

Forum Structure Changes Coming on 11/8!

Dynamics 365 Community Platform update – Oct 28

Subscribe to this forum!

Select categories

Leaderboard > Finance

Featured topics

Product updates