SQL::Abstract generate insert with select query

Question

insert into table1(id, text) select (max(id)+1), "Something" from table1

How to generate this query with SQL::Abstract or SQL::Abstract::More?

Answer 1

You can do this:

use strict;
use warnings;
use SQL::Abstract;

my $sa = SQL::Abstract->new;
my $select = $sa->select('table1', "MAX(id) + 1, 'Something'");
my $full_sql = "INSERT INTO table1 (id, text) $select";

But really, why would you? Your example is trivial and, as pointed out by others, better served by just writing the SQL. If you intended "something" to be a variable, you could do this:

my $new_id = $sa->select('table1', 'MAX(id) + 1');
my $data = {
   id => \["($new_id)"],
   text => $some_value_from_elsewhere
};
my ($sql, @binds) = $sa->insert('table1', $data);

Answer 2

Your SQL statement contains, as Rachcha pointed out, some flaws. I assume you're trying something like

insert into table1(id, text) values((select (max(id)+1) from table1), "Something")

If you want to add new data into table1 with a unique ID you should really, really consider to add a primary key to your id field in the database.

Updated answer

The old anwser does create a valid SQL-statement regarding data and syntax but the prepare and execute function in DBI does some magic with the values.

If i run the query like this:

my $query = "insert into table1 values((select (max(id)+1) from table1), 'Something')";
$dbh->do($query);

The query will be left as is while executed. When I instead use the prepare and execute, DBI automatically puts the values between single quotes like 'this' which result in:

insert into table1(id, text) values('(select (max(id)+1) from table1)', 'Something');

Obviously, this will print the value as text (if that's a valid data type). This does in my opinion not mean that the generated query from the code is faulty rather than the unwanted quotations from DBI. I can't find an anwser to if it's possible to remove them. The question is if DBI supports an insert ... select statement at all. If not, you won't be able to use SQL::Abstract (or prepare/execute) with these querys.

You could build this as a two step rocket as long as you don't have multiple transactions to your database which could cause duplicates.

my $select_stmt = $sql->select('table1', '(max(id)+1)'):
# Prepare with $stmt
# Fetch your result and declare $max_id, e.g.
my $sth = $dbh->prepare($select_stmt);
$sth->execute();

my $max_id = 0;
my @max_id_arr = $sth->fetchrow_array();
$max_id = $max_id_arr[0];

die($max_id . " is 0") unless($max_id);

my %data = (
    id => $max_id,
    text => 'Something',
);

my($insert_stmt, @insert_bind) = $sql->insert('table1', \%data);

Old answer

However, to reply to your question, this should do the trick:

my $max_id = $sql->select('table1', 'max(id)+1');
my %data = (
    id => "($max_id)",
    text => "Something",
);
my($stmt, @bind) = $sql->insert('table1', \%data);

printf("My SQL statement was %s\nThe values was %s\n", $insert_stmt, join(", ", @insert_bind));

This will print

My SQL statement was INSERT INTO table1 ( id, text) VALUES ( ?, ? ) The values was (SELECT max(id)+1 FROM table1), Something

Answer 3

While I use SQL::Abstract a lot in my code, I would not recommend it in your example. Mainly because it is sometimes more readable and maintainable if you write SQL-Code directly. And because you have to make sure that the order of the insert fields exactly match the order of the select fields.

You could however use the $sql->where() method to extract the where-part from your select query, if you have to:

    my $stmt1 = <<'END_SQL';
    INSERT INTO table1(id, text) 
    SELECT (MAX(id)+1) id, 'Something' text
    FROM table1
    END_SQL

    my ($stmt2,@binds) = $sql->where( key1=>$value1, key2=>$value2 );

And then simply concatenate the two SQL-statements.