Insert/Update in same table

[Amit.Sagpariya]

Hi All,

I have a requirement where i need to count the number of rows into a table for given date.

following is my table

id date
==== ========
HG67 05-apr-2009
KL90 05-apr-2009

Lets say, the gievn date is 06-apr-2009. The table does not contain any data, the count query will return 0. In this situation, i should insert the data into table from previous day.

I mean, after executing query, the data should look like...


id date
==== ========
HG67 06-apr-2009
KL90 06-apr-2009
HG67 05-apr-2009
KL90 05-apr-2009

I need to use previous day's data, modify the date part to current date while keeping original data as it is.

Can some one help me to writing this query?

[srikanth.mss]

insert into product_details
select P.id, TRUNC(SYSDATE)
from product_details P
where trunc(P.date)= trunc(sysdate-1)
and NOT EXISTS
(
SELECT 1 FROM PRODUCT_DETAILS P1
WHERE P1.ID = P.ID AND
P.TRUNC(DATE) = TRUNC(SYSDATE)
)

Try this one,

Regards,
Srikanth.