1

I have 14k unique ids in a .txt file, based on which I want to delete data from Amazon RedShift table. I have tried keeping the records inside IN clause, but it is not working. The query keeps on running for a long time

Eg:

delete from <table_name> where <primary_key_column> in (1,2,3....,14000);

Yogesh Joshi
  • 23
  • 4

2 Answers2

0

Referring to this post might help. It looks like using such a large 'in' statement is going to take the Db quite some time to process.

I would personally do something programmatically with a loop and break this down into multiple statements.

You could follow the programmatic approach another way and use a transaction.

Something like:

begin read write;

delete from <table> where <col> = 1
...
delete from <table> where <col> = 14000

commit;
JerodG
  • 1,248
  • 1
  • 16
  • 34
0

I would recommend:

  • Load the text file as a new table
  • Delete records where the ID is in that table

Something like:

DELETE FROM table1
WHERE id IN (SELECT id FROM table2)
John Rotenstein
  • 241,921
  • 22
  • 380
  • 470