perl - 比较来自 2 个数据库的两个 SQL 查询的两列 [英] perl - compare two column of two SQL query from 2 databases

问题描述

在 perl 中，如果数据库 1 中的名称和数据库 2 中的名称相等，我想将数据库 2 中的年份和部分插入数据库 1

in perl, i want to insert year and parti from database 2 to database 1 if name from database 1 and name from database 2 is equal

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

感谢您的回复，

我的 perl 和 sql 代码是 这里

my code of perl and sql are here

推荐答案

从一个数据库读取并在第二个数据库中更新:

Read from one database and update in the second one:

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";
}

这篇关于perl - 比较来自 2 个数据库的两个 SQL 查询的两列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！