sql - Update new table using other table data with few modification in new table -

i have 1 table named "input_data" below, table going take input values other table.

**tn         start_date   operator** 12345       12-may-2015       123 67890       14-may-2015       456 

and table "output_data" below, table updated per values coming "input_data"

**source_id effective_date  expiration_date provider_id**   12345     01-jan-2014                       555 

as 12345 source_id present in output_data table, in case want update entry source_id 12345 coming "input_data" table such earlier entry in "output_data" table expire new entry’s start_date. , row inserted new entry. hence "output_data" should below:

**dest_id      effective_date   expiration_date provider_id**   12345          01-jan-2014    12-may-2015       555   12345          12-may-2015                      123   67890          14-may-2015                      456 

i new sql, , trying query below above task:

select a.tn, a.start_date, a.operator, nvl(a.expiration_date,          to_date('31-12-  9999 00:00:00','dd-mm-yyyy hh24:mi:ss'))      (select tn,start_date,destination,     lead(effective_date) on (partition tn order effective_date )  expiration_date             (select op.dest_id tn, op.effective_date start_date,                  op.provider_id destination          output_data op, input_data ip op.dest_id=ip.tn           union          select ip.tn tn, ip.start_date effective_date, ip.operator destination           input_data ip) t)           effective_date != nvl(expiration_date,to_date('31-12-9999','dd-mm-yyyy hh24:mi:ss')) 

but above query not getting executed not able check output.

before downvoting read this: question posted tags sqlserver, mysql , oracle, oracle answer not ready yet, working on it

this sql-server:

update output_data  set expiration_date = (select start_date   input_data   tn = output_data.source_id);   insert output_data(source_id, effective_date,provider_id)  select tn,start_date,operator input_data; 

sqlfiddle

exact same code works mysql:

sqlfiddle