multiple delete queries

[djack2]

Is there a way to delete more than 1 record at a time? I'm
using oracle 8 and CF 4.5. I need to delete several records at once but haven't found a way to do it. If I just do more than 1 delete "query" it times out. I need to do something like this: delete from my_table where id = '1', where id = '2', where id = '5'. This however doesn't work either.
Oracle says that the query is not properly ended. I've tried puttting a ; at the end but that doesn't work either. Any thoughts?

 

[GunJack]

Hey djack2,

I think this is what you want.

delete from my_table where
id = '1' or id = '2' or id = '5'

Another way that I believe will work is
delete from my_table where
id in ('1','2','5')

This assumes that id is a text field. If it's numeric, just remove the single quotes around the values like this.

id = 1 or id = 2 or id = 5

Hope this helps,
GJ

 

[iqof188]

This is one way to do it:

DELETE FROM my_table
WHERE (id = '1' or id = '2' or id = '5');

Another way to do it is:

<cfset deletelist=&quot;1,2,5&quot;>

DELETE FROM my_table
WHERE ID IN (#deletelist#);

The second option doesn't work in Access 97- though, but since you use Oracle it shouldn't be a problem
Hope this helps...


<webguru>iqof188</webguru>

 

[iqof188]

GunJack, it happened again, we posted at the same time! Good answer again though ;-)

<webguru>iqof188</webguru>

 

[GunJack]

Hehe, that's cool, 2 almost identical answers at the same time

GJ

 

[djack2]

I love this forum! You guys are great! Thanks to both of you for all your help.