CODEWITHSUNDEEP
sqlperlmariadb
sdgr hjgh
sdgr hjgh

Reputation: 45

perl - compare two column of two SQL query from 2 databases

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

i want the result:

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

thanks for any response,

my code of perl and sql are here

Upvotes: 1

Views: 233

Answers (2)

Rick James
Rick James

Reputation: 142298

UPDATE database1.truc
    JOIN database2.truc2  ON database1.truc.name = database2.truc2.name
    SET database1.truc.year = database2.truc2.year
        database1.truc.parti = database2.truc2.parti;

No binding, no copying data into arrays, no loss of id, etc.

Upvotes: 2

choroba
choroba

Reputation: 241858

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]);
}

Tested with:

#!/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";
}

Upvotes: 3

Related Questions