Query problem

Thread View: borland.public.interbase.sql

2 messages

[T] Back to Threads [V] Tree View [G] Group Articles

2 total messages Started by Donato Verrastro Fri, 29 Nov 2002 11:09

#11250

Author: Donato Verrastro
Date: Fri, 29 Nov 2002 11:09

23 lines
1869 bytes

I have a problem to write a query. Can you help me?
I have a two table:
CREATE TABLE1 (COD NUMBER(10,0),
                               YEAR NUMBER(10,0),
                               SUBCOD NUMBER(10,0),
                               ..,
                               PRIMARY KEY(COD,YEAR,SUBCOD));
and table2
CREATE TABLE2 (COD NUMBER(10,0),
                                YEAR NUMBER(10,0),
                                SUBCOD NUMBER(10,0),
                                ..,
                                PRIMARY KEY(COD,YEAR,SUBCOD));
I want to insert in TABLE1 all record of TABLE2 that are not present in
TABLE1.
How can I do this query?
I have write the following query but i think that it isn't correct.
INSERT INTO TABLE1
  SELECT .... FROM TABLE2
  WHERE COD||'-'||YEAR||'-'||SUBCOD NOT IN (SELECT
COD||'-'||YEAR||'-'||SUBCOD FROM TABLE2);
Thank you in advance
Donato Verrastro

Re: Query problem

#11256

Author: Fausto Brador
Date: Sun, 01 Dec 2002 04:35

45 lines
2492 bytes

Hi Donato!
Try
Insert Into table1
         Select *
            from table2 a
          where not Exists(Select 1
                                      From table1 b
                                     Where b.cod = a.cod
                                         And b.year = a.year
                                         And b.subcod = a.subcod);

Bye

FBrador


"Donato Verrastro" <donato.verrastro@libero.it> wrote in message
news:3de74a9e@newsgroups.borland.com...
> I have a problem to write a query. Can you help me?
> I have a two table:
> CREATE TABLE1 (COD NUMBER(10,0),
>                                YEAR NUMBER(10,0),
>                                SUBCOD NUMBER(10,0),
>                                ..,
>                                PRIMARY KEY(COD,YEAR,SUBCOD));
> and table2
> CREATE TABLE2 (COD NUMBER(10,0),
>                                 YEAR NUMBER(10,0),
>                                 SUBCOD NUMBER(10,0),
>                                 ..,
>                                 PRIMARY KEY(COD,YEAR,SUBCOD));
> I want to insert in TABLE1 all record of TABLE2 that are not present in
> TABLE1.
> How can I do this query?
> I have write the following query but i think that it isn't correct.
> INSERT INTO TABLE1
>   SELECT .... FROM TABLE2
>   WHERE COD||'-'||YEAR||'-'||SUBCOD NOT IN (SELECT
> COD||'-'||YEAR||'-'||SUBCOD FROM TABLE2);
> Thank you in advance
> Donato Verrastro
>
>
>
>

Thread Navigation

This is a paginated view of messages in the thread with full content displayed inline.

Messages are displayed in chronological order, with the original post highlighted in green.

Use pagination controls to navigate through all messages in large threads.

Back to All Threads