Select and delete as one statement. RRS feed

  • Question

  • Hi

    Is it possible to select and delete rows in a single statement?

    Here is what I have now:

    insert into myTable (col1, col2, col3) select col1, col2, col3 from tableX where col1 in (1, 2, 3, 4)

    delete from from tableX where col1 in (1, 2, 3, 4)

    The problem is that search on the tableX is executed two times first for select next for delete.

    Is it possible to execute this with only one search on the tableX? It could work like this: search rows, return rows, delete searched rows. In this way search would be executed only one time.



    Monday, January 14, 2013 6:07 AM


All replies