Write Delete statement on Select in Oracle Database

Question

I would like to know how to prepare a Delete statement based on the below select query.

select TO_DATE(TO_CHAR(OGUPMJ+1900000),'YYYYDDD') jdedate from testdta.F5847036 WHERE TO_DATE(TO_CHAR(OGUPMJ+1900000),'YYYYDDD') < (sysdate-30);

I am not able use

DELETE from TESTDTA.F5847036 where jdedate in (select TO_DATE(TO_CHAR(OGUPMJ+1900000),'YYYYDDD') jdedate from testdta.F5847036 WHERE TO_DATE(TO_CHAR(OGUPMJ+1900000),'YYYYDDD') < (sysdate-30));

or

DELETE from TESTDTA.F5847036 where TO_DATE(TO_CHAR(OGUPMJ+1900000),'YYYYDDD') jdedate in (select TO_DATE(TO_CHAR(OGUPMJ+1900000),'YYYYDDD') jdedate from testdta.F5847036 WHERE TO_DATE(TO_CHAR(OGUPMJ+1900000),'YYYYDDD') < (sysdate-30));

Please give us more background on what "not able to use" means: to you get a parser error, or a runtime error? Is it a matter of syntax or a matter of query plan choice? — Cee McSharpface
– Cee McSharpface, Commented Sep 22, 2017 at 8:17

Cee McSharpface · Accepted Answer · 2017-09-22 08:21:17Z

2

The identifier jdedate is used as an alias in the SELECT query. It defines the column name in the result set. Column name aliases are not useful with delete (because there are no columns to return) and therefore not permitted by the syntax of the delete statement.

Remove the two occurences of the jdedate alias in the delete statement.

answered Sep 22, 2017 at 8:21

Cee McSharpface

8,7133 gold badges43 silver badges90 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Write Delete statement on Select in Oracle Database

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related