1

I have a list of Id(string). I want to delete records from like this:

DELETE FROM MyTable WHERE ID=:Id

But, I don't want to to call the DB repeatedly; instead want to pass the Id collection as comma separated string and execute the above query on the Oracle server at one shot!

Any help will be highly appreciated.

Thanks in advance.

Alex Poole
  • 183,384
  • 11
  • 179
  • 318
user2948533
  • 1,143
  • 3
  • 13
  • 32
  • Where is the list of values coming from, and where are you executing the command from - another PL/SQL block; Java; PHP; Pro\*C; somewhere else? – Alex Poole Oct 03 '16 at 11:50
  • its from my C# code, string Id="Newyork,Paris,Jakarta"; like this. – user2948533 Oct 03 '16 at 11:52
  • Mmm, not sure about C#. You can pass arrays [in Java](http://stackoverflow.com/a/21034016/266304) and [in C/C++](http://stackoverflow.com/questions/18603281/oracle-oci-bind-variables-and-queries-like-id-in-1-2-3/18675238#18675238); for C# I think it might depend which driver you're using, but I have no experience with that - might point you in the right direction though... Essentially you're looking to bind a nested table collection, and can then use the table collection operator with a join, or the `member of` syntax. – Alex Poole Oct 03 '16 at 12:02
  • Note that a "comma-separated list" is not a thing in SQL / PL/SQL, and there are no built-in split/join functions. You'd do better passing an actual collection as @AlexPoole suggests. – William Robertson Oct 03 '16 at 14:21

1 Answers1

3

With a comma-separated list you can use a function like this to split your IDs:

  select regexp_substr(:id,'[^,]+',1,level) element
      from mytable
    connect by level <= length(regexp_replace(:id,'[^,]+')) + 1

So the final query :

DELETE FROM MyTable WHERE ID in ( select regexp_substr(:id,'[^,]+',1,level) element
                                   from mytable
                                 connect by level <= length(regexp_replace(:id,'[^,]+')) + 1)
Kobi
  • 2,494
  • 15
  • 30