CODEWITHSUNDEEP
sqloracle-database
dunantsm
dunantsm

Reputation: 47

Write Delete statement on Select in Oracle Database

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));

Upvotes: 0

Views: 55

Answers (1)

Cee McSharpface
Cee McSharpface

Reputation: 8726

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.

Upvotes: 2

Related Questions