Oracle delete row using sequence number

My DB knowledge is quite limited and I am trying to delete a row with the following query:

DELETE FROM table WHERE column in (select sequenc开发者_运维问答e.CURRVAL FROM DUAL);

This is used in a .sql to clean up the database after integration tests are run in Maven. I have Googled it but still haven't found an answer for the delete statement to work. Any help is appreciated!

You can't use a CURRVAL in a DELETE statement, as you can read here: http://www.orafaq.com/wiki/ORA-02287

But since you are using SQL scripts, you can do it like this in SQL*Plus:

SQL> create table t( id number);

Table created.

SQL> create sequence seq;

Sequence created.

SQL> insert into t (id) values (seq.nextval);

1 row created.

SQL> column i new_value curseqval
SQL> select seq.currval i from dual;

         I
----------
         1

1 row selected.

SQL> delete from t where id = &curseqval;
old   1: delete from t where id = &curseqval
new   1: delete from t where id =          1

1 row deleted.

Regards,
Rob.

1) sequence.CURRVAL from DUAL is a single discrete value; the use of IN is inappropriate

2) because you are referencing sequence.CURRVAL you need not use a subquery, you may access this value directly.

try:

DELETE FROM table WHERE column = sequence.CURRVAL;

assuming the sequence is being used as a primary key (somewhere) this will result in the deletion of the most recently insert key record or if the column is a foreign key, then only those records that are related to the most recent record.

oops... I've done enough delete queries that I didn't check my work. While I have never thought to use sequence values in a delete it never occurred to me that you couldn't do it.

this, however will work:

declare del_key number;
begin
select  seq_so_test.currval into del_key from dual;
delete from t_so_test where id = del_key;
 end;