update and insert

[raj123]

how do i do the following

i have two tables

tableone
---------
column1
column4
columnd1
columnd2

tabletwo
----------
column1
column4
columnd1

if find same value in column1 in both tables then i want to update tableone.columnd1 with a calue of tabletwo.column4 otherwiase insert a new row

how do i do this?

pls help

[Scorby]

Look at the merge command.

[raj123]

thanks

i just need for update .and also the column tableone.columnd1
is date column i need to move 2 months from the date

[Scorby]

In your original post you said "otherwiase insert a new row". So do you need to do an insert or are you just doing updates ?

Merge is perfectly capable of handling functions in the update/insert:

drop table merge_test1;
drop table merge_test2;

create table merge_test1 (a varchar2(5), b date);
create table merge_test2 (a varchar2(5), b date);

insert into merge_test1 values ('A', add_months(sysdate, -4));
insert into merge_test1 values ('B', add_months(sysdate, -5));
insert into merge_test1 values ('C', add_months(sysdate, -6));
insert into merge_test1 values ('D', add_months(sysdate, -7));

insert into merge_test2 values ('A', null);
insert into merge_test2 values ('C', null);

merge into merge_test2 m2 using merge_test1 m1 
on (m2.a = m1.a) 
when matched then update 
set m2.b = add_months(m1.b, 2) 
when not matched then 
insert (a, b) 
values (m1.a, add_months(m1.b,2))

[raj123]

thanks i will try this