SQL "Delete From"

Question

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')

BonusCup · Answer

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

SQL_Delete_From.yxzp

JasonOppong · Answer

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

BonusCup · Answer

@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