Delete statement from database based on the values from csv file

[tskora]

Hi, I have a table in database where I need to process data from csv file. The thing is that those csv might contain updated data that is already in the database. It looks like this:

Database column week:

202301

202302

202303

202304

Csv File contains:

202305

202301

So in order to process data correctly I need first to do the delete statement of 202305(no records will be deleted cause it's not in the db) and 202301 (some records will be deleted cause data present in db).

Is there a way to do this in Alteryx? I've searched for the solution but they did not matched my scenario.

[acarter881]

Hello, @tskora.

I'm not sure what the exact SQL syntax is for your use case, but I believe something like the below would work.

DELETE FROM CUSTOMERS WHERE CUSTOMERNAME IN ('Around the Horn', 'Chop-suey Chinese')

I suppose you would have to transform the values from the format you have to a string that is concatenated to Start with a single apostrophe, separated by a single apostrophe, comma, and a single apostrophe, then ends with an apostrophe.

[TimN]

If the database table has a Primary key "week" then the existing row in the database table will be overwritten.  Doesn't that get you what you need?  Use the Output Option "Update: Insert if new".  You must have a PK on the week column.

[simonaubert_bd]

Hello,

It will depends of the database you're using. Some allows the upsert (update or insert) but not the majority.

https://help.alteryx.com/20231/designer/write-data-db-tool

For the others, you will have to manually compare with join then union the unmodified, the modified and the new records.

Best regards,

Simon