#!/usr/bin/perl

use PApp::SQL;

local $PApp::SQL::DBH = PApp::SQL::connect_cached "dinfo::", "DBI:mysql:dinfo";

#die "you should not run this program unless you know exactly what you are doing";

sql_exec "update ort set data='Ummendorf' where data='ummendorf'";
sql_exec "update ort set data='Mettenheim' where data='mettenheim'";

#for my $col (qw(vorname strasse vorwahl plz ort)) {
   my $st = sql_exec \my($data, $count), "select data, count(*) as c from $col group by 1 having c > 1 order by 2 desc";
   while ($st->fetch) {
      my @id = sql_fetchall "select id from $col where data = ?", $data;
      my $lowest = shift @id;
      print "$col,$data,$count: @id => $lowest\n";
      for (@id) {
         #sql_exec "update row set $col = ? where $col = ?", $lowest, $_;
         #sql_exec "delete from $col where id = ?", $_;
      }
   }
}

for my $col (qw(
  name
  vorname
  zusatz1
  zusatz2
  zusatz3
  vorwahl
  strasse
  haus
  plz
  ort
  branche
)) {
   if (0) {
      my ($id, $count) = sql_fetch "select $col, count(*) from row group by 1 order by 2 desc limit 1";
      warn "$col => ($id) * $count\n";
      next unless $id;
      next;
      sql_exec "update $col set id = 0 where id = ?", $id;
      sql_exec "update row set $col = 0 where $col = ?", $id;
   }
}