http://www.perlmonks.org?node_id=1139989

shadowsong has asked for the wisdom of the Perl Monks concerning the following question:

Dear Monks,

I am trying to ascertain whether it's possible to include a function call within an SQL insert statement which obtains its values from a select statement.

The SQL statement looks like this:

INSERT INTO TABLE2 (COLUMN1, COLUMN2, COLUMN3) SELECT COLUMN1, COLUMN2, COLUMN3 FROM TABLE1 WHERE COLUMN4='FOOBAR'

My script will therefore have something like this

... my $statement = "INSERT INTO TABLE2 (COLUMN1, COLUMN2, COLUMN3) \n". "SELECT COLUMN1, COLUMN2, COLUMN3 \n". "FROM TABLE1 WHERE COLUMN4=?"; my $rv = $dbh->do($statement, {}, 'foo');

Now, what I'd like to do is to modify COLUMN3 before inserting it into TABLE2 by passing it into a subroutine... but I'm not sure how to go about it (without splitting the INSERT statement into two parts and handling them separately).

What I have below isn't going to work but hopefully it should give you an clear picture of what I'd like to achieve

... my $statement = "INSERT INTO TABLE2 (COLUMN1, COLUMN2, ". my_subrt(COLUMN3) .") \n". "SELECT COLUMN1, COLUMN2, COLUMN3 \n". "FROM TABLE1 WHERE COLUMN4=?"; my $rv = $dbh->do($statement, {}, 'foo');

Many thanks