Help in refining the sql..

[anilreddy76]

Hi,

I've 2 tables "Test1" and Test2"

Test1 Table contains 3 columns

id,
Location,
Vendor,
Input_date

Test2 Table contains same 3 columns

Location,
Vendor,
Input_date.

I want to insert/update the records in Test1 table based on the values from "Test2" table.

I want to check whether new locations or vendors are present in "Test2" table before
doing insert into "Test1" table and also to see whether for particular locations
in "Test2" table whether there are any new vendors and insert the same into "Test1" table.

For update I need to check if there are any updates with respect to the vendors for particular location.

I've written the following Sql's for Insert/Update,but I think it is not meeting my requirements.

Insert into Test1(id,location,vendor,INPUT_DATE)
select vendor_sequence.nextval,t.* from (select DISTINCT(location), vendor, sysdate from Test2 where
location not in(select location from test1) and vendor not in(select location from test1))t;

Update Test1 vn set(location,vendor,INPUT_DATE)
=
(select DISTINCT(location),vendor,SYSDATE from Test2 sdata where vn.Vendor= sdata.vendor and vn.location = sdata.location);

Please advise me on this.

Thanks,
Anil

[chazzy]

I don't really see the point in the update, you're only modifying the date.

[anilreddy76]

I don't really see the point in the update, you're only modifying the date.

I got the solution for this.

Thanks,