By Paul Murphy, author of The Unix Guide to Defenestration
Sometimes it's simply much easier to dump a table to an ordinary file, truncate the table to zero rows, edit the file, and then reload the table than it is to do change the data in situ using SQL.
I started doing this in the days when U2K didn't let you change table definitions on the fly so doing things like adding a column could be a major undertaking. Today I still do this when referential integrity issues aren't involved because it's faster, easier, and less error prone than hacking SQL.
Usually what you want to do is something like getting rid of hidden duplicates where the use of the row number in the key lets the system pretend non unique rows are unique or adding a column with null values except where some other value in the row meets a specified condition. These are trivial tasks for Perl. For example something like:
$FS = ',';
while (<>) {
chomp;
@Fld = split(/[,\n]/, $_, 9999);
$X = $_;
if (($Fld[pick_one] == some_condition) && (other comparisons) ) { $X = $X . ', NULL';}
else { $X = $X . ',some_value'; }
print $X;
can be made to do as complicated a set of checks and changes as you might wish and the truncate/reload guarantees you a clean table with shiny new indexes.