Re: Efficient select/insert

[Jonathan Mangin]

----- Original Message ----- 
From: "Eamon Daly" <[EMAIL PROTECTED]>
To: "Jonathan Mangin" <[EMAIL PROTECTED]>
Cc: <mysql@lists.mysql.com>
Sent: Thursday, May 19, 2005 1:17 PM
Subject: Re: Efficient select/insert


my $sql = sprintf <<'EOF', join(',', @array);
SELECT col2, col3, col4
FROM table1
WHERE col1 IN (%s)
EOF
my $sth = $dbh->prepare($sql);
$sth->execute() or die $sth->errstr();
____________________________________________________________
Eamon Daly
Thanks, that works (I'll have to read a bit to learn why)
except for one thing I didn't mention. (Everybody Lies :)
How ugly is this?
$sql = "INSERT into $table2";  # dynamic name with $user_id as root
$sql .= sprintf <<'EOF', join(',', @array);
(col2, col3, ...)
SELECT col2, col3, ...
FROM table1
WHERE col1 IN (%s)
EOF

----- Original Message ----- 
From: "Jonathan Mangin" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Cc: <mysql@lists.mysql.com>
Sent: Thursday, May 19, 2005 11:52 AM
Subject: Re: Efficient select/insert


> This would be even faster if you could concatenate all of the 
> elements
of
> @array into a single list then you could say:
>
> #My PERL skills are non existent so you need to write this part.
> #I am assuming that @array is holding a list of string values.
>
> foreach my $element (@array) {
> @araylist += "'" + $element + "',";
> }
> @arraylist = left(@arraylist, length(@arraylist) -1)
>
> #that's to remove the trailing comma at the end of the list
>
> my $sql = "select col2, col3, col4 from table1
>           where col1 IN (?)";
> my $sth = $dbh->prepare($sql);
> $sth->execute(@arraylist) or die $sth->errstr();
>
I've finally gotten around to this. It doesn't appear to be
possible using a placeholder.
$list  # a string of an unknown number of CS numeric primary keys.
$sql = "insert into table2
       (col2, col3, ...)
       select col2, col3, ...
       from table1
       where id in (?)";
$sth = $dbh->prepare($sql);
$sth->execute($list) || die ...
This inserts only the first item from $list.
$sth->execute(2,4) complains about mismatched number of bind variables.
An array (@list) does the same thing (only worse:)
where id in ($list)" returns an SQL syntax error.
Should I just fall back or am I missing something?
foreach $item (@list) {
  $sql = "insert into table2 ...
Thanks,
Jon
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: 
http://lists.mysql.com/[EMAIL PROTECTED]



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/[EMAIL PROTECTED]