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

I use SQLite with Perl. I need to duplicate some rows. All values should remain the same exept for one. I wander if there is an easiest way as to what I'm doing now: read the content of the entire row, put all values in variables and insert (INSERT) a new row with the same values exept for the one I want to change. Note: I do't want just to update the row. After the operation I need two records.

Replies are listed 'Best First'.
Re: Sqlite DBI duplicate record
by MidLifeXis (Monsignor) on Mar 07, 2013 at 18:19 UTC

    Something like this: 

    insert into table (col1, col2, ...)
(
    select newval_or_expression, col2, ... 
    from table
    where ...
)

    [download]
    Note: SQLite may or may not allow this nested operation. (thanks roboticus)

    --MidLifeXis

[reply]
[d/l]

      MidLifeXis:

      It doesn't have to be nested. SQLite is fine with insert (cols) select ...:

      14:28 METABASE.DFLT:
create table foo (a int, b text);
0 affected, 1 sec.

create table bar (a int, b text);
0 affected, 0 sec.

insert into bar (a, b) values (1, 'bing')
1 affected, 0 sec.

insert into bar (a, b) values (2, 'bop')
1 affected, 0 sec.

insert into foo (a, b) select a, b from bar
2 affected, 0 sec.

select * from foo
a b
1 bing
2 bop
2 rows
elapsed time: 0

      [download]

      ...roboticus

      When your only tool is a hammer, all problems look like your thumb.

[reply]
[d/l]
[select]
Re: Sqlite DBI duplicate record
by roboticus (Chancellor) on Mar 07, 2013 at 19:34 UTC

    welle:

    You should be able to do that:

    $STI = $DB->prepare('insert into foo (a, b) values (a, b)');
$STS = $DB->prepare('select a, b from bar');
$STS->execute();
while (my $ar = $STS->fetchrow_arrayref()) {
    $ar->[1] = defined($ar->[1]) ? $ar->[1] : '=null=';
    $STI->execute(@$ar);
}

    [download]

    ...roboticus

    When your only tool is a hammer, all problems look like your thumb.

[reply]
[d/l]

Back to Seekers of Perl Wisdom