| 1 | #!/usr/bin/perl |
1 | #!/usr/bin/perl |
| 2 | |
2 | |
| 3 | use PApp::SQL; |
3 | use PApp::SQL; |
| 4 | |
4 | |
| 5 | local $PApp::SQL::DBH = PApp::SQL::connect_cached "dinfo::", "DBI:mysql:dinfo"; |
5 | local $PApp::SQL::DBH = PApp::SQL::connect_cached "dinfo::", "DBI:mysql:dinfo"; |
|
|
6 | |
|
|
7 | #die "you should not run this program unless you know exactly what you are doing"; |
|
|
8 | |
|
|
9 | sql_exec "update ort set data='Ummendorf' where data='ummendorf'"; |
|
|
10 | sql_exec "update ort set data='Mettenheim' where data='mettenheim'"; |
|
|
11 | |
|
|
12 | #for my $col (qw(vorname strasse vorwahl plz ort)) { |
|
|
13 | my $st = sql_exec \my($data, $count), "select data, count(*) as c from $col group by 1 having c > 1 order by 2 desc"; |
|
|
14 | while ($st->fetch) { |
|
|
15 | my @id = sql_fetchall "select id from $col where data = ?", $data; |
|
|
16 | my $lowest = shift @id; |
|
|
17 | print "$col,$data,$count: @id => $lowest\n"; |
|
|
18 | for (@id) { |
|
|
19 | #sql_exec "update row set $col = ? where $col = ?", $lowest, $_; |
|
|
20 | #sql_exec "delete from $col where id = ?", $_; |
|
|
21 | } |
|
|
22 | } |
|
|
23 | } |
| 6 | |
24 | |
| 7 | for my $col (qw( |
25 | for my $col (qw( |
| 8 | name |
26 | name |
| 9 | vorname |
27 | vorname |
| 10 | zusatz1 |
28 | zusatz1 |
| … | |
… | |
| 15 | haus |
33 | haus |
| 16 | plz |
34 | plz |
| 17 | ort |
35 | ort |
| 18 | branche |
36 | branche |
| 19 | )) { |
37 | )) { |
|
|
38 | if (0) { |
| 20 | my ($id, $count) = sql_fetch "select $col, count(*) from row group by 1 order by 2 desc limit 1"; |
39 | my ($id, $count) = sql_fetch "select $col, count(*) from row group by 1 order by 2 desc limit 1"; |
| 21 | warn "$col => ($id) * $count\n"; |
40 | warn "$col => ($id) * $count\n"; |
| 22 | next unless $id; |
41 | next unless $id; |
|
|
42 | next; |
| 23 | sql_exec "update $col set id = 0 where id = ?", $id; |
43 | sql_exec "update $col set id = 0 where id = ?", $id; |
| 24 | sql_exec "update row set $col = 0 where $col = ?", $id; |
44 | sql_exec "update row set $col = 0 where $col = ?", $id; |
|
|
45 | } |
| 25 | } |
46 | } |
| 26 | |
47 | |
