Deleting Multiple Rows in SQL

Like (0) Share

Report

Posted on 13 Jul 2020 13:51:33 by EpicTriffid

364

Afternoon,

I'm trying to use Remove/Removeif to delete several rows in my SQL table that relate to a particular user ID. I've tried the All operator, but I think the problem is with delegation which, while set at 2000, is having trouble with my 8000+ items table.

I thought the easiest way was to filter the datasource at the beginning of the Remove statement, but while I can use a clear collect and see that the filter works and is picking up all the records I need deleting, the Remove does not seem to like me using the filter:

RemoveIf(
 Filter('[dbo].[Units]', Entry_ID = recordtoDelete.Entry_ID),
 Entry_ID = recordtoDelete.Entry_ID
);

There must be a way to do this. I'd be talking about 20 records to be deleting at max. I can't use Flow at the moment to help with this as I'm still in the grace period from the license changes, and I was told my MS Support that adding a new flow to my app would void the grace period.

Am I missing something obvious?

Categories:

All responses (3)

Answers (1)

Verified answer

v-yutliu-msft on 14 Jul 2020 at 06:09:00

Like (0)

Report
Re: Deleting Multiple Rows in SQL

Hi @EpicTriffid ,

Do you want to remove multiple records?

If so, I suggest you know more about Remove function and RemoveIf function.

1)Remove:

syntax:

Remove( DataSource, Record1 [, Record2, ... ] [, All ] )

The first parameter is the data source, the second parameter is the record that you want to remove.

However, please notice that you need to list the record that will be moved one by one .

It's not supported to directly use Filter() to represent all the records to remove.

So you should use formula like this:

Remove('[dbo].[Units]', First(Filter('[dbo].[Units]', Entry_ID = recordtoDelete.Entry_ID)), //the first record Last(FirstN(Filter('[dbo].[Units]', Entry_ID = recordtoDelete.Entry_ID),2)), //the second record .... )

2)RemoveIf

syntax:

RemoveIf( DataSource, Condition [, ... ] )

Since in your issue, you do not know the record number that you need to remove, so I more suggest you use RemoveIF function.

The first parameter is data source, the second parameter is condition.

However, in your original formula:

Filter('[dbo].[Units]', Entry_ID = recordtoDelete.Entry_ID), Entry_ID = recordtoDelete.Entry_ID

You have used the condition in the first parameter and you use the same condition in the second parameter.

Please try this formula:

RemoveIf( '[dbo].[Units]', Entry_ID = recordtoDelete.Entry_ID );

Here's a doc about these two functions in details for your reference:
https://docs.microsoft.com/en-us/powerapps/maker/canvas-apps/functions/function-remove-removeif

Best regards,
EpicTriffid 364 on 13 Jul 2020 at 14:02:41

Like (0)

Report

Re: Deleting Multiple Rows in SQL

@mdevaney

Thank you for your reply!

I was thinking the same thing, but I'm getting an error saying "Expected 2 or more arguments". I think it wants me to specify the item to remove, but there is no one item to remove, i need a few to be removed.
mdevaney 29,987 Moderator on 13 Jul 2020 at 13:55:50

Like (0)

Report
Re: Deleting Multiple Rows in SQL

@EpicTriffid

I would expect your could just use this shorter version of your formula since FILTER can be delegated

Remove(Filter('[dbo].[Units]', Entry_ID = recordtoDelete.Entry_ID));

Link to MS Documentation on Remove

https://docs.microsoft.com/en-us/powerapps/maker/canvas-apps/functions/function-remove-removeif

---
Please click "Accept as Solution" if my post answered your question so that others may find it more quickly. If you found this post helpful consider giving it a "Thumbs Up."