How to insert and update in single query?..

[amitsukte]

I have two tables most_used_tab and most_used.
most_used_tab is master table in which data is stored depending on the data of most_used

tables are like this

SQL> SELECT * FROM MOST_USED_TAB;
TNAME COUNTS USED_DATE RESTART_DATE
----------------------------------------------------
TAB1 10 10-FEB-12 10-FEB-12 12.00.00.000 AM
TAB2 20 10-FEB-12 10-FEB-12 12.00.00.000 AM
TAB3 10 10-FEB-12 10-FEB-12 12.00.00.000 AM
TAB1 15 11-FEB-12 10-FEB-12 12.00.00.000 AM
TAB2 10 11-FEB-12 10-FEB-12 12.00.00.000 AM

SQL> SELECT * FROM MOST_USED;

TNAME COUNTS USED_DATE RESTART_DATE
---------------------------------------------------------
TAB1 30 12-FEB-12 10-FEB-12 12.00.00.000 AM
TAB2 35 11-FEB-12 10-FEB-12 12.00.00.000 AM
TAB3 10 10-FEB-12 10-FEB-12 12.00.00.000 AM
TAB4 15 11-FEB-12 10-FEB-12 12.00.00.000 AM


Task 1
if(most_used_tab.restart_date=most_used.restart_da te) then

{

most_used.counts-sum(most_used_tab.counts) group by tname
and resulting row should be inserted into most_used_tab
while inserting it should check
if
(most_used_tab.used_date =most_used.used_date and
most_used_tab.tname=most_used.tname) then

update most_used_tab.counts as most_used_tab.counts on same used_date + ( most_used.counts - sum(most_used_tab.counts) group by tname )

and suppose if tname of most_used doesn't exist in most_used_tab then the row should be inserted as it is in to most_used_tab.


}

so the finally the most_used_tab should look like this


TNAME COUNTS USED_DATE RESTART_DATE
----------------------------------------------------
TAB1 10 10-FEB-12 10-FEB-12 12.00.00.000 AM
TAB2 20 10-FEB-12 10-FEB-12 12.00.00.000 AM
TAB3 10 10-FEB-12 10-FEB-12 12.00.00.000 AM
TAB1 15 11-FEB-12 10-FEB-12 12.00.00.000 AM
TAB2 25 11-FEB-12 10-FEB-12 12.00.00.000 AM
TAB1 5 12-FEB-12 10-FEB-12 12.00.00.000 AM
TAB4 15 11-FEB-12 10-FEB-12 12.00.00.000 AM


please give some tips for this..