Apart from the syntax problem, MySQL versions earlier than 4.1 (which is far from stable at the moment) do not support subqueries.
Here's a workaround to such limitation.
#!/usr/bin/perl -w
use strict;
use DBI;
my ($dbh, $rows);
$dbh = DBI->connect('dbi:mysql:test',
'myusername',
'mypassword',
{RaiseError=>1}) ||
die "Error opening database: $DBI::errstr\n";
#
# First, get destination from your table
#
my ($destination) = @{$dbh->selectcol_arrayref(qq{
SELECT destination
FROM checkin
WHERE firstname='Henry' AND lastname='Rollins'}
)}
# || die "destination not found\n"; # wrong
or die "destination not found\n"; # correct
#
# Notice that the script fails if destination is not found.
#
#
# then, use $destination to update the table
#
$rows = $dbh->do(qq{UPDATE checkin
SET destination = '$destination'
WHERE firstname='Bill' AND lastname='Gates'})
|| die "Couldn't update record : $DBI::errstr";
print "$rows row(s) updated in checkin";
$dbh->disconnect || die "Failed to disconnect\n";
A few comments:
- You are using UPDATE and then printing "rows added."
Be aware that the UPDATE statement modifies an existing record. It does not add rows.
- What you are doing with your SQL statements is taking one column (destination) from a row identified by name and surname, and then inserting such column into another record identified by another name/surname pair.
Make sure that this is really what you want to do.
- You are using a MySQL installation with "root" user and no password.
This is a huge security risk. Please fix it as fast as you can.
update - Fixed a small bug in the script.
_ _ _ _
(_|| | |(_|><
_|