Thread: can this be done in one SQL selcet statement?!

I have two table:
-Table1: one column of type TEXT containing label for nodes in a graph

-Table 2: two columns of type TEXT. first column contains node labels
in a graph. second a list of node labels that the node label in column
one is connected to.

Example:
    Table1:
         "NODE1"
         "NODE2"

    Table 2:
          "NODE1"    "NODE2 NODE3"
          "NODE2"    "NODE4 NODE3"

Goal:
     split column2 in table2 to individual node names, find a unique
list of all node names obtained after splitting column2 of table2 and
insert the ones not already in table1 in table1.


Thanks in advance.

S

First, I think the table design is probably not the best way to do this.
In the relational database world, Table 2 probably should look like this:

NODE1    NODE2
NODE1    NODE3
NODE2    NODE4
NODE2    NODE3


Then you could do:

INSERT INTO table1 SELECT DISTINCT column2 FROM table2 WHERE column2 NOT
IN (SELECT column1 FROM table1);

Greg

sasan3@gmail.com wrote:
>
> I have two table:
> -Table1: one column of type TEXT containing label for nodes in a graph
>
> -Table 2: two columns of type TEXT. first column contains node labels
> in a graph. second a list of node labels that the node label in column
> one is connected to.
>
> Example:
>     Table1:
>          "NODE1"
>          "NODE2"
>
>     Table 2:
>           "NODE1"    "NODE2 NODE3"
>           "NODE2"    "NODE4 NODE3"
>
> Goal:
>      split column2 in table2 to individual node names, find a unique
> list of all node names obtained after splitting column2 of table2 and
> insert the ones not already in table1 in table1.
>
>
> Thanks in advance.
>
> S
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: In versions below 8.0, the planner will ignore your desire to
>        choose an index scan if your joining column's datatypes do not
>        match

Thanks for your reply.

I agree with your table structure suggestion, but for now, if I had a
function split_text that took in a "text" type and returned a "set of
text" basically breaking up the text field, how would I use that in the
SQL statement you wrote? Thanks.

Si

Greg Mitchell wrote:
> First, I think the table design is probably not the best way to do this.
> In the relational database world, Table 2 probably should look like this:
>
> NODE1    NODE2
> NODE1    NODE3
> NODE2    NODE4
> NODE2    NODE3
>
>
> Then you could do:
>
> INSERT INTO table1 SELECT DISTINCT column2 FROM table2 WHERE column2 NOT
> IN (SELECT column1 FROM table1);
>
> Greg
>
> sasan3@gmail.com wrote:
> >
> > I have two table:
> > -Table1: one column of type TEXT containing label for nodes in a graph
> >
> > -Table 2: two columns of type TEXT. first column contains node labels
> > in a graph. second a list of node labels that the node label in column
> > one is connected to.
> >
> > Example:
> >     Table1:
> >          "NODE1"
> >          "NODE2"
> >
> >     Table 2:
> >           "NODE1"    "NODE2 NODE3"
> >           "NODE2"    "NODE4 NODE3"
> >
> > Goal:
> >      split column2 in table2 to individual node names, find a unique
> > list of all node names obtained after splitting column2 of table2 and
> > insert the ones not already in table1 in table1.
> >
> >
> > Thanks in advance.
> >
> > S
> >
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 9: In versions below 8.0, the planner will ignore your desire to
> >        choose an index scan if your joining column's datatypes do not
> >        match
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: don't forget to increase your free space map settings