Help please, this script runs through fine gives me confirmation in an HTML page with the correct variables written in but does not up date the data base. The message may be SQL syntax but I haven’t got a clue! Any one got any Ideas. The ‘M’ Is the value I was sending for $Sex and ‘Y’ Is the value I was sending for $Maturity. So it must be that bit. I get the same with Commas added. Sex =?,
Maturity =?,
Age =?,
Length =?,
Notes =?
I can not find an example of using IN this way with variables has anyone done this? Any comments please, or references, C pan, Dev Shed and MySql manual I have examined, I may have missed something. I have probably looked so hard I have missed the obvious. Anything please Chris



error in your SQL syntax near '(UPDATE Guide SET Sex ='M',
[Fri Jan 09 17:13:31 2004] [error] [client 127.0.0.1] Maturity ='Y',
[Fri Jan 09 17:13:31 2004] [error] [client 127.0.0.1] ' at line 1 at C:/Program Files/Apache Group/Apache2/cgi-bin/Guide_edit.pl line 28.


#!c:\perl\bin\perl.exe
use CGI ":standard";
$Code=param("Code");
$Name=param("Name");
$Family=param("Family");
$Sex=param("Sex");
$Maturity=param("Maturity");
$Age=param("Age");
$Length=param("Length");
$Notes=param("Notes");
open (OUTFILE, ">>Guide_edit.txt");
print OUTFILE "$Code $Name $Family $Sex $Maturity $Age $Length $Notes\n";
close (OUTFILE);
use DBI;
$DBH = DBI->connect("DBI:mysql:Guide") or die "\n ($DBI::err) : $DBI::errstr\n";
my $query = $DBH ->prepare(qq{(UPDATE Guide SET Sex =?,
Maturity =?,
Age =?,
Length =?,
Notes =? )
WHERE ? IN (Select Code
FROM Guide
WHERE Code like ?
AND Name like ?
AND Guide.Family like ?)
});

$query->execute($Sex, $Maturity, $Age, $Length, $Notes,$Code,$Code,$Name, $Family);
$query->finish();
$DBH->disconnect();

In case any one else hits this brick wall it is because:

WHERE x IN (subselect) will only work in mysql 4.1+

I will have to start all over again! Chris