perl-比较来自2个数据库的两个SQL查询的两列
在perl中，如果数据库1的名称和数据库2的名称相等，我想将数据库2的year和parti插入数据库1
database 1 database 2
table truc table truc2
id name year parti id name year parti
1 Lapin 14 Lapin 2014 MODEM 118 Koala 33 Murène 1347 EELV 14 Murène 2 Ragondin 4218 SP 3 Ragondin 3 Koala 1512 CPNT 我想要结果：
database 1
table truc
id name year parti
1 Lapin 2014 MODEM
118 Koala 1512 CPNT
14 Murène 1347 EELV
3 Ragondin 4218 SP 感谢您的任何回复，
问题来源于stack overflow
版权声明：本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。
-
从一个数据库中读取并在第二个数据库中进行更新：
my $query2 = $db2->prepare('SELECT id, name, sex, year, parti FROM truc2'); my $query1 = $db1->prepare('UPDATE truc' . ' SET year = ?, parti = ? WHERE name = ?');
$query2->execute;
while (my @row = $query2->fetchrow_array) { $query1->execute($row[3], $row[4], $row[1]); } 经过测试：
#!/usr/bin/perl use warnings; use strict; use utf8; use feature qw{ say };
use open IO => ':encoding(UTF-8)', ':std';
use DBI;
my $db1 = DBI->connect('dbi:SQLite:dbname=:memory:', "", "", { sqlite_unicode => 1 }); $db1->do('CREATE TABLE truc' . ' (id INT, name TEXT, sex VARCHAR, year INT, parti TEXT)'); my $db2 = DBI->connect('dbi:SQLite:dbname=:memory:', "", "", { sqlite_unicode => 1 }); $db2->do('CREATE TABLE truc2' . ' (id INT, name TEXT, sex VARCHAR, year INT, parti TEXT)');
my $insert1 = $db1->prepare('INSERT INTO truc (id, name, sex)' . ' VALUES (?, ?, ?)'); $insert1->execute(@$_) for [ 1, 'Lapin', 'M'], [118, 'Koala', 'F'], [ 14, 'Murène', 'A'], [ 3, 'Ragondin', 'F'];
my $insert2 = $db2->prepare('INSERT INTO truc2 (id, name, sex, year, parti)' . ' VALUES (?, ?, ?, ?, ?)'); $insert2->execute(@$_) for [14, 'Lapin', 'A', 2014, 'MODEM'], [33, 'Murène', 'F', 1347, 'EELV'], [ 2, 'Ragondin', 'M', 4218, 'SP'], [ 3, 'Koala', 'F', 1512, 'CPNT'];
my $query2 = $db2->prepare('SELECT id, name, sex, year, parti FROM truc2'); my $query1 = $db1->prepare('UPDATE truc' . ' SET year = ?, parti = ? WHERE name = ?');
$query2->execute;
while (my @row = $query2->fetchrow_array) { $query1->execute($row[3], $row[4], $row[1]); }
my $verify = $db1->prepare('SELECT * from truc'); $verify->execute; while (my @row = $verify->fetchrow_array) { say "@row"; }
2019-11-15 17:31:16赞同 展开评论