Perl DBI update tables

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

I am copying data from table 1 and insert into table 2, the issue is I don't want to insert all the data I received from table 1 value for value, I want to update some fields and leave other fields to auto increment

Table structure is  

id    int(11)
source_code    varchar(20)
stream_code    char(100)
client_id    int(11)
stream_oauth_id    int(11)
name    varchar(200)
url    varchar(2500)
status_code    char(10)
stream_data    longtext
feed_db    varchar(500)
feed_table    varchar(200)
last_update    datetime
report_db    varchar(500)
sla    smallint(5) unsigned
exclude_request    varchar(200)
owned    tinyint(1)
push    tinyint(1) unsigned
[download]

The table information is

 id: 447
    source_code: FACEBOOK
    stream_code: 281434322dd4734
      client_id: 53
stream_oauth_id: 422
           name: Seer
            url: Old Beer
    status_code: DISABLED
    stream_data: NULL
        feed_db: production_feed002
     feed_table: FACEBOOK_28111aa734
    last_update: 2015-02-05 13:23:19
      report_db: production_report002
            sla: 0
exclude_request: CONVERSATIONS
          owned: 1
           push: 0
[download]

My insert statement is below so I am not updating all the values, is this a problem? The error I am getting is below, I am not sure why are there 12 values needed
DBD::mysql::st execute failed: called with 15 bind variables when 12 are needed at /home/rdscopy.pl line 131. DBD::mysql::st execute failed: called with 15 bind variables when 12 are needed at /home/rdscopy.pl line 131.

$activate_stream =$dbh4->prepare("INSERT INTO stream (source_code, str
+eam_code, client_id, name, url, status_code, stream_data, feed_db, fe
+ed_table, last_update, report_db, sla, exclude_request, owned, push) 
+VALUES(?, ?, 10, ?, ?, DISABLED, ?, production_feed002, ?, ?, ?, ?, ?
+, ?, ?)");

 while (my  @activate_results =  $activate_info >fetchrow_array() )
                                                                      
+  {
                                                                      
+  $activate_stream->execute(@activate_results) or die $activate_strea
+m->errstr;
                                                                      
+  }
                                                                }
$activate_stream->finish();
[download]

id is an auto-increment so we let MySQL assign a value
I want to make client id = 10.
Skip the stream_oauth_id for now. That should get created if/when we refresh access tokens on the stream. It may not be necessary in most test cases.
feed_db will be also assigned in the insert statement

Comment on Perl DBI update tables Select or Download Code

Replies are listed 'Best First'.
Re: Perl DBI update tables by kcott (Archbishop) on Dec 07, 2016 at 00:20 UTC
G'day cbtshare, The error message seems pretty clear. You're passing `@activate_results` (which has 15 elements) to `execute()` (which is only expecting 12 elements). This is untested, so make sure you check this yourself, but it looks like you want: `$activate_stream->execute(@activate_results[0, 1, 3, 4, 6, 8 .. 14])` [download] See "perldata: Slices" if you're unfamiliar with the `@array[...]` construct (it's called an array slice). — Ken	[reply] [d/l] [select]
Re^2: Perl DBI update tables by cbtshare (Monk) on Dec 07, 2016 at 04:56 UTC
thank you , this is what I wanted	[reply]
Re: Perl DBI update tables by huck (Prior) on Dec 07, 2016 at 00:22 UTC
It doesnt help when you dont show all the basics ... what is the assignment for $activate_info? my guess is `$activate_info =$dbh?->prepare("SELECT * FROM ...");` and you are getting more variables than you need. Remember how i showed you how to select only the variables you need and in the proper order in your last mistake? use that method	[reply] [d/l]
Re^2: Perl DBI update tables by kcott (Archbishop) on Dec 07, 2016 at 01:10 UTC
"... what is the assignment for $activate_info?" ++ That's a very valid point. In my response, I assumed that was selecting the 15 columns named in the `INSERT` statement (`source_code, ..., push`). I then removed the 3 elements from `@activate_results` for which placeholders did not exist (i.e. the indices 2, 5 & 7) creating the array slice. That could be an incorrect assumption on my part. — Ken	[reply] [d/l] [select]
Re^3: Perl DBI update tables by cbtshare (Monk) on Dec 07, 2016 at 01:17 UTC
true, but I am selecting 15 values, I am not sure where its getting 12 from	[reply]
Re^4: Perl DBI update tables by kcott (Archbishop) on Dec 07, 2016 at 02:43 UTC
Re^2: Perl DBI update tables by cbtshare (Monk) on Dec 07, 2016 at 01:16 UTC
I meant to show that but forgot to post the info: `my $activate_info= $dbh->prepare("SELECT source_code, stream_code, cli +ent_id, name, url, status_code, stream_data, feed_db, feed_table, las +t_update, report_db, sla, exclude_request, owned, push FROM stream WH +ERE id = ?"); $activate_info->execute($client_ID);` [download]	[reply] [d/l]
Re^3: Perl DBI update tables by huck (Prior) on Dec 07, 2016 at 04:58 UTC
Why are you selecting (and including in the to-insert array) the 3 variables that you have already inserted constants for in the insert statement (client_id, status_code, and feed_db) ? as kcott has pointed out you have 12 "?" symbols, so you need 12 members in the to-insert array	[reply]