Re: COPY command, linking foreign keys

On 12/19/05 9:27 AM, "Srinivas Iyyer" <srini_iyyer_bio(at)yahoo(dot)com> wrote:

> Table: gene_tab
>
> gene_id | gene_name
> ---------+-----------
> 1 | AARS
> 2 | AGC1
> 3 | APOA5
> 4 | APOB
> 5 | ATP13A2
> 6 | C9orf106
> 7 | CCNI
> 8 | CENTG3
> 9 | CITED4
> 10 | GPR24
>
> Table: gene_exp
>
> gene_id | gene_exp
> ---------+----------
> (0 rows)
>
>
>
> Table : gene_exp_temp
>
> temp_name | temp_exp
> -----------+----------
> AARS | 100
> AGC1 | 200
> APOA5 | 201
> APOB | 202
>
>
> question : Inserting into gene_exp from gene_exp_temp:
>
>

Srini,

I like the construct:

INSERT INTO table1 (col1,col2,...)
SELECT ....

This lets me see what I am inserting before I do so....

Something like:

INSERT INTO gene_exp (gene_id,gene_exp)
SELECT gt.gene_id,
ge.temp_exp
FROM
gene_temp gt join gene_exp_temp ge
on gt.temp_name=ge.gene_name;

I haven't tested this, but this might make the thought process a little
easier. One thing it looks like you need (you have probably already thought
of this) is an "experiment ID" somewhere in your gene_exp table....

Sean