Beefy Boxes and Bandwidth Generously Provided by pair Networks
Pathologically Eclectic Rubbish Lister
 
PerlMonks  

Sqlite DBI duplicate record

by welle (Beadle)
on Mar 07, 2013 at 17:54 UTC ( #1022281=perlquestion: print w/ replies, xml ) Need Help??
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.

Comment on Sqlite DBI duplicate record
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 ... )
    Note: SQLite may or may not allow this nested operation. (thanks roboticus)

    --MidLifeXis

      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

      ...roboticus

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

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); }

    ...roboticus

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

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://1022281]
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others about the Monastery: (8)
As of 2015-07-04 06:08 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    The top three priorities of my open tasks are (in descending order of likelihood to be worked on) ...









    Results (57 votes), past polls