Insert syntaxis not working

[daniel salgado]

Hello

I am running perl v5.8.8
mysql 5.0.19, for pc-linux-gnu (x86_64)

I am trying to make a relation between two tables, 1:n.

I have both tables

mysql> desc ensembl_set
+----------------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------------+-------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | | auto_increment |
| ensembl_gen_id | varchar(15) | YES | MUL | | |
| id_vectores | int(4) | YES | | | |
+----------------+-------------+------+-----+---------+----------------+

mysql> select * from ensembl_set limit 3;
+----+-----------------+-------------+
| id | ensembl_gen_id | id_vectores |
+----+-----------------+-------------+
| 1 | ENSG00000000003 | NULL |
| 2 | ENSG00000000005 | NULL |
| 3 | ENSG00000000419 | NULL |
+----+-----------------+-------------+


Is the NULL I want to change for the primary key of another table this one

mysql> desc vectores;
+--------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | | auto_increment |
| numero | int(4) | YES | | | |
| vector | varchar(18) | YES | MUL | | |
+--------+-------------+------+-----+---------+----------------+

mysql> select * from vectores limit 3;
+----+--------+--------------------+
| id | numero | vector |
+----+--------+--------------------+
| 1 | 1 | 000000000011111001 |
| 2 | 1 | 100100011111111011 |
| 3 | 1 | 111101011001111111 |
+----+--------+--------------------+


So I parse a file with this information.
(the problematic sentence is:I
NSERT INTO ensembl_set(id_vectores) VALUES($idvectores) SELECT * FROM ensembl_set WHERE ensembl_gen_id='$ensg)

the script :

while (<IN2>)
{
if ($_=~ m/^g/) {next;}
my @a=split (/\t/);
my $ensg=shift@a; #here is the ensembl_gen_id
my $vector=join ('', @a);
chomp $vector; #here is the vector
$sth = $dbh->prepare("SELECT id FROM vectores where vector='$vector'");
$sth->execute;
while (my $idvectores = $sth->fetchrow_array())
{
print $idvectores,"\t",$ensg,"\n"; #this is to see in the screen this values, they exist
$sth2 = $dbh->prepare("INSERT INTO ensembl_set(id_vectores) VALUES($idvectores) SELECT * FROM ensembl_set WHERE ensembl_gen_id='$ensg'");
$sth2->execute;
}
}

The output:
DBD::mysql::st execute failed: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT * FROM ensembl_set WHERE ensembl_gen_id='ENSG00000000003'' at line 1 at vectores_en_bd.pl line 36, <IN2> line 2.
DBD::mysql::st execute failed: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT * FROM ensembl_set WHERE ensembl_gen_id='ENSG00000000003'' at line 1 at vectores_en_bd.pl line 36, <IN2> line 2.


It looks as a mysql problem (not perl) I have tried lots of diferent

"INSERT INTO ensembl_set(id_vectores) VALUES($idvectores) SELECT * FROM ensembl_set WHERE ensembl_gen_id='$ensg'"

with and without "", calling the tables ensembl_set.id_vectores. Also Selecting either id_vectores or ensembl_gen_id rather than *.

Any suggestions?

thanks for reading this and thanks for any reply.