Hi all,

I have 2 tables X and Y

X: contains following data

Table Name | Days
A | 10
B | 8
C | 100
D | 45

Y: contains following structure

Col1| col2 | col 3| col4 | col5 | ……..|Table_Name


How do I delete records from table Y basing on the number in Days column in Table X.

Hope I am clear.


Thanks
Mark.

delete from table y t1
where
t1.table_name in
( select
t2.table_name
from
x t2
where
t2.days = :value );

---

I guess this should work, rewrite it to a select:

select t1.table_name from table y t1
where
t1.table_name in
( select
t2.table_name
from
x t2
where
t2.days = :value );

In order to verify that the correct rows will be deleted.

/pF

You should store the date of the record in a column of Y to be able to let this work.

This is what this SQL script does: delete all records from table Y if the date of the record (Y.DA_CREATE) plus the number of X.Days days it should be kept is smaller or equal to the current date.

DELETE
FROM Y
WHERE EXISTS
(SELECT *
FROM X
WHERE X.Table_Name = Y.Table_Name and
Y.DA_CREATE + X.DAYS DAY <= CURRENT DATE
)
;

Y.DA_CREATE + X.DAYS DAY <= CURRENT DATE action
2004/12/02 0 2004/12/03 delete
2004/12/02 1 2004/12/03 delete
2004/12/02 2 2004/12/03 don't delete

Assuming that X.DAYS = 1 means: keep it only the day the record was made (i.e. 2004/12/02).

Wim