SQL "Delete From"

[Matthew]

hello, all

i am trying to run a dynamic SQL query that will delete rows from a table when the row ID is in a list.. i swear i got it to work before, but now i cant figure it out

i'm using a dynamic input tool with the following  query, and the dynamic input replaces 'xxx' with the list of IDs, but it's not working

delete from 
  [Database].[Schema].[Table] 
where 
  [Record_ID] in ('xxx')

[kathleenmonks]

Hi @Matthew,

It looks like the user in this discussion solved this problem using an Update WHERE clause in the dynamic input and putting the "delete from" section in the connection query section.

Let me know if this solves your problem!

[BonusCup]

@kathleenmonks I'm looking into this same issue the OP had.  When I go to the link you provided it takes me to a "This Content was Archived".  Do you know how I can get to the page you were trying to share?  TIA

[JasonOppong]

Hi I am looking for a solution similar to this, was this ever able to get resolved ?

[BonusCup]

@JasonOppong I was able to get this working.

I have an app where end users can enter jobIDs they want deleted from a table.  A requirement is if they have multiple jobs, they need to separate them by a comma. The workflow adds the appropriate single quotes and commas where needed for each ID.

I have a text input with 3 lines in it:
delete

from <your table>

where jobID in ('999999')

I cross tab that into a single record with 3 fields, from the app side I rewrite the where clause to include all of the IDs that need to be deleted.  I then concat those 3 fields into one single line and name it 'query'.  In the Dynamic input > Edit, my query is:
Select 1; DELETE FROM tbl0200_notifications WHERE JobID = '999999'

When you save it, you most likely get an invalid select statement error but it still saves.  Back in the Dynamic Input, select the Modify SQL Query and select "Replace a Specific String" from the Add dropdown.  In the 'Text to Replace', I entered DELETE FROM tbl0200_notifications WHERE JobID = '999999' and in the Replacement Field, select 'query' from the dropdown.

Attached is the sample app workflow with the concatenate macro I use.  Hope this helps.