Results 1 to 2 of 2
  1. #1
    Join Date
    May 2004
    Posts
    9

    Unanswered: 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?
    Last edited by Triple A; 01-23-05 at 08:31.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •