Deleting Multiple Rows in SQL

(0) Share

Report

Posted on 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:

Building Power Apps

I have the same question (0)

All responses (3)

Answers (1)

Sort by

mdevaney 29,991 Moderator on at

Like (0)

Report
Copy link

Link copied!
@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."

Was this reply helpful? Yes No
EpicTriffid 364 on at

Like (0)

Report
Copy link

Link copied!

@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.

Was this reply helpful? Yes No
Verified answer

v-yutliu-msft Microsoft Employee on at

Like (0)

Report
Copy link

Link copied!
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,

Was this reply helpful? Yes No