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:
Show quoted text
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:
Show quoted text
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 NODE3Then 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