Name is required.
Email address is required.
Invalid email address
Answer is required.
Exceeding max length of 5KB

SQL Component - Delete

Hi Im trying to Delete rows in a table by using the SQL Component.
i have two inputs to SQL component one is tables that i want to delete some rows based on the rows i have in another input component

here my query that is throwing error:

DELETE FROM $T{SFDC_CASE} where ID in (select ID from $T{DELETED})


The above gives output if i use SELECT instead of DELETE.

3 Community Answers

Matillion Agent  

Laura Malins —

Hi Hanumanth

You can only run select statements from the SQL Transformation component.

However there is a SQL component in the Orchestration jobs which you can use to run the delete

Thanks
Laura


Hanumanth Andela —

Thanks for your confirmation Laura.

But how to use the output results from a filter component in Transformation Job and use that as a input in Orchestration job to run the delete command in SQL SCript component


Matillion Agent  

Ian Funnell —

Hi Hanumanth,

This isn’t possible to do using a Matillion transformation component. As my colleague mentioned, you’ll need to use the Orchestration SQL Script component in order to perform a delete. You can’t pass the keys to delete into an orchestration job in that way, so you’d need to replicate your filter in SQL in the orchestration component. Please see this document for the required syntax.

By the way, you may find that it’s quicker overall to create a second table without the records you intend to delete, and then use the second table. You can do this in a Transformation job, and this is mainly why we didn’t include a Delete transformation.

Best regards,
Ian

Post Your Community Answer

To add an answer please login