in reply to Increment DB using Perl
Update: You could also do it by inserting into a new table with values from the old one, like this:
use strict; use warnings; use DBI; # Set up database connection my $user = "content"; my $pass = "content"; my $dbh = DBI->connect("DBI:ODBC:LocalServer",$user, $pass, {RaiseErro +r => 1}); my $new_id = 0; # Assumes the new table request2 has the same layout as the first one. # The ID should be declared as a primary key for this table. # I assume it was not declared that way in table request. my $sth = $dbh->prepare(q{SELECT * FROM request}); # There should be a placeholder for each value in the table. my $sth2 = $dbh->prepare(q{INSERT INTO request2 VALUES(?,?,?)}); my $sql_data = ($sth->execute() || die $sth->errstr); while (my $pointer = $sth->fetchrow_arrayref) { # Assumes the ID field is the first field. $pointer->[0] = $new_id; $sth2->execute(@{$pointer}); $new_id++; }
In summary, it was a bad mistake to define an SQL table without unique keys, and the best way to recover is by getting the data into a new table.
|
|---|