CODEWITHSUNDEEP
perldbi
Arav
Arav

Reputation: 5247

Perl DBI insert and select

I want to copy a single record from a table, modify some of the fields in the record and insert into the same table. The table has 90 columns.

Thought of using insert..select in one statement but there are 90 columns and i need to tell the column name in the select query. How can i do it in a better way in perldbi. Pls provide me an example.

Upvotes: 2

Views: 1676

Answers (1)

dbenhur
dbenhur

Reputation: 20408

Fetch and cache the column names for your table using the NAME statement attribute

my $sth = $dbh->prepare('SELECT * FROM my_table where 1=0');
$sth->execute;
my $cols = $sth->{NAME};

then use $cols to construct your insert...select using some replacement function to inject your modifications in the select.

my %mods_for_column = ( 'foo' => 'foo+10', 'bar' => 'trim(bar)' );
my $inscols = join(',', @$cols);
my $selcols = join(',', 
  map { exists($mods_for_column($_)) ? $mods_for_column($_) : $_ } @$cols
);
my $sql = "insert into my_table ($inscols) select $selcols from my_table where mumble...";

Upvotes: 3

Related Questions