0

I have a table as below:

CREATE TABLE product
(
  PRODUCT   NVARCHAR2(20)                       NOT NULL,
  TYPE   NUMBER(5)                           NOT NULL,
  ID     NUMBER(10)                          NOT NULL,
  addDATE    DATE                                NOT NULL,
  TITLE     NVARCHAR2(80)                       NOT NULL
)

I have to delete records from the table based on the column type and addDate. There are 40 types of products and each product has a different deletion criteria. Below is the query I am currently using;

DELETE from PRODUCT where 
(type =1 and trunc(ADDdate) <  add_months( sysdate, -24 ))
OR 
(type =2 and trunc(ADDdate) <  add_months( sysdate, -12 ))
OR 
(type =3 and trunc(ADDdate) <  add_months( sysdate, 12 ))
.
..
.
.
OR 
(type =49 and trunc(ADDdate) <  add_months( sysdate, 1 ));

Could you please suggest me an efficient way of implement this other than the above mentioned query.

user2077648
  • 951
  • 7
  • 27
  • 42
  • First, I removed the mysql tag, because the syntax is clearly Oracle. Second, you should have the deletion criteria in a table. Please show that table in the question. – Gordon Linoff Nov 15 '15 at 15:44
  • I have mentioned already that the records have to be deleted based on the where condition in the delete query. My question is if there is any other efficient way of writing the where condition? – user2077648 Nov 15 '15 at 15:46
  • @user2077648 Define efficient. If you're looking for the simplest syntax, then Gordon's answer is probably best. If you're looking for best performance, then what you have is probably best. – Jon Heller Nov 15 '15 at 17:13

1 Answers1

1

You should have a table that has the deletion criteria . . . which appears to be a number of months. Hence:

delete from product
    where trunc(AddDate) < (select add_months(sysdate, dtt.deletionmonths)
                            from DeleteTypeTable dtt
                            where dtt.type = product.type
                           );
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • So, here a separate table DeleteTypeTable has to be created with columns type and deletionmonths ? – user2077648 Nov 15 '15 at 15:53
  • @user2077648 . . . If you have a type, you should have a reference table that describes it. – Gordon Linoff Nov 15 '15 at 16:02
  • yes there is a table but it does not have a column deletionmonths , hence this solution involves modifying the table structure. Is there anything else you can suggest which does not involve adding an additional column deletionmonths to the reference table? – user2077648 Nov 15 '15 at 16:04
  • @user2077648 . . . Putting this information in the `type` tables seems like the *right* thing to do. Much better to have the information there in a reference table than embedded in code somewhere, somehow. – Gordon Linoff Nov 15 '15 at 23:29