Deleting rows before inserting into SQL Server 2016

[Anonymous]

Using this query, I find the date for which I want all rows with a greater date deleted. The table is sorted by this date.

SELECT TOP 1 ThisDate, ID
FROM [Database].[dbo].[Table]
WHERE ThisDate < CONVERT(DATE, GETDATE()-30)

ORDER BY ThisDate DESC, ID DESC

This is what I want to do with ThisDate:

DELETE FROM  [Database].[dbo].[Table] 

WHERE ThatDate > ThisDate

Despite much searching, I can find no clear explanation on how to do this with tMSSqlOutput or any other component. I can use tSetGlobalVar to pass a value into a query, but which component can execute a delete statement? I really don't care how this can be done, but I want to do it within Talend MDM DI instead of calling a stored procedure.

Thanks 

[Anonymous · Accepted solution]

You need to think about this in a slightly different way. Try using a tMSSQLRow and just use a query like this....

DELETE FROM  [Database].[dbo].[Table] 
WHERE ThatDate > (SELECT TOP 1 ThisDate, ID
FROM [Database].[dbo].[Table]
WHERE ThisDate < CONVERT(DATE, GETDATE()-30)
ORDER BY ThisDate DESC, ID DESC)

You can parameterise your query however you want using context variables or the globalMap. 

Alternatively (if the data comes from a different db to the db having data deleted), you could load your found date into a globalMap (as you mentioned) and then use it in your delete, again in a tMSSqlRow.