Deletion of records from a table

[gmv]

Hi ,

I'm looking for inputs to identify and delete records from table 1.

I have 2 tables .Table 1 and Table 2. I have found a way to identify the inserts and updates from table 1 using the output tool (update ,insert if new option) and with left outer and union could accomplish this. 

However , I'm trying to find a way to do deletion of records into table 2, ( I have to identify the records that are removed from Table 1 on daily basis and perform those deletes into table 2 ). I have a strategy to use select all from table 2 and left outer join with table 1 where the primary key is null , to identify the deletes, But not sure which transformation or tool i should use post this to get these deletes . Looked at output tool and there is no such option to pass filter in the sql. 

Can some one pls suggest any work around here . Need to implement this by next week and out of options

I have added the current working workflow here in order to better understand my need and help update or add the delete flow into this under similar lines.

Appreciate any inputs !

[Francisco_Gonzalez]

Hi, 

I think that you need to add an extra field, after the select tool. This field is the date of Update/Insert, with this field you will have the same table 1 plus a field "last modified date", and this date update for all insert and update row.

Then for the rest, the register than no have been updated or inserted, the date or time will be different, you can be deleted using a post SQL statement "delete from table_1 where [last_modified_date] <> current_date()"

Whit this you will delete register of the "right join tool" of your workflow.

Thx.

[gmv]

Hi - Thank you very much for your reply. You mean add a formula tool and create a column, I understand the logic , but not able to determine the tools to use as i just started using alteryx. if you can give me pointers on the tools after select that i should be using to achieve this that would be of great help and i will try the same. 

Right now im trying to do formula tool to achieve this. 

[Francisco_Gonzalez]

Yes, 

Of course, I add your workflow with the necessary modifications.

Thx.

[gmv]

Thank you very much ! Changed and testing now .

However, the output tool could not recognize today as a column as this column does not exist on this table. Hence throwing below error. I cannot modify the structure of the table. So any thoughts just to use this for select  and fix this error?

Output Data (6) Error opening " No Columns Returned.

[Francisco_Gonzalez]

Yes, 

You must add this column in your PostgreSQL admin console, add column table.

http://www.postgresqltutorial.com/postgresql-add-column/

Thx.

[gmv]

Thank you again for quick response. Unfortunately i cannot modify the table structure. So i was thinking of using join and perform delete in the post sql . Do you think that will work . Any thoughts on that approach.

Have attached a sample version. but there is some gap here, Basically im trying to create 2 flows ( one is for the upsert (update and insert rows into table 2 based on the changes from table 1 . this is flow 1 which works fine today )

Second flow is for the identifying the deletes that have happened in table 1, and perform the same deletion into table 2. 

I thought below sql logic might help that.  i do have a challenge here. these are 2 different databases . im comaring against. So in my output tool i cannot configure 2 databases in my driver. So looks i may have t create a temp table with deletion records and use that in the post sql. 

DELETE FROM table2(database 2)
LEFT OUTER JOIN table1 ( database 1)
WHERE table1."ROLE_ID" IS NULL

Not sure how to implement this logic i think that may work with the tools in alteryx. 

attached this workflow too. Anyway you could help enhance this ?

Example :

[Francisco_Gonzalez]

Hi, 

Now, I understand. Then you want one reply of table 1 in the table 2. then why not only you delete the data of table 2 and append de data of table 1 than you want.

It can do with the option of output tool delete data & append.

Thx.

[gmv]

Sure thanks ,yes i did consider that option. But since this table is most crucial and if any failures during delete might cause entire access system to break down i did not want to do full refresh of the table. Rather i just want to touch only the impacted rows , be it insert or update or delete .

Hence trying the options with joins. Do you think any better way to do this using joins ?

[gmv · Accepted solution]

Created a flow for delete and update seperately and using joins and post sql query , was able to make it work by referring the records to be deleted from temp table. thnx 

---

@gmv wrote:

Sure thanks ,yes i did consider that option. But since this table is most crucial and if any failures during delete might cause entire access system to break down i did not want to do full refresh of the table. Rather i just want to touch only the impacted rows , be it insert or update or delete .

Hence trying the options with joins. Do you think any better way to do this using joins ?

---