Friday, February 10, 2012

Comparing two tables with different fields

I have two tables which only have one key field in common. I want to synchronise them in the sense that the key items from one table also end up in the second table. I dont know how to do this. This is the situation on the tables:

table1
Fruit | color | taste |
Apple | Red | sweet |
pear | brown | sour |
orange| orange | sweet|
kiwi | brown | sweet

table2
Fruit | Costprice | retailprice
Apple | 1 | 2
Pear | 2 | 4

Field1 is present in both tables but has more records in the first table than in the second. I want items in table1 that aren't present in table 2 to be added to table2. In other words, if more fruit is listed in table A I want it to be added to table 2. In this case, the orange and the kiwi should be added to the second table:

table2
Fruit | Costprice | retailprice
Apple | 1 | 2
Pear | 2 | 4
orange| NULL | NULL
kiwi | NULL | NULL

Con someone help me?insert into table2 (fruit)
select fruit from table1
where not exists (select 1 from table2 where fruit=table1.fruit)

or

insert into table2 (fruit)
select fruit from table1
where fruit not in (select fruit from table2)

No comments:

Post a Comment