ON DUPLICATE KEY UPDATE error in MySQL? (Perl/SQL)?

J

jdr0317

New member
So I have a query that takes in 5 fields, 4 as a unique primary key (date, id1, id2, id3), and a total field.
I'm iterating through a hash variable in Perl in order to dump the values into a table, but I seem to be hitting a brick wall.

Despite the fact that I have an ON DUPLICATE KEY UPDATE clause, I still get a primary key error.
The exact code:

foreach my $key1 (keys %{$id1_id2_id3}) {
my $k1=$dbh->quote($key1);
my $tmp1=$id1_id2_id3->{$key1};
foreach my $key2 (keys %{$tmp1}) {
my $k2=$dbh->quote($key2);
my $tmp2=$tmp1->{$key2};
foreach my $key3 (keys %{$tmp2}) {
my $k3=$dbh->quote($key3);
my $total=$id1_id2_id3->{$key1}->{$key2}->{$key3}->[0];
my $cSQL="INSERT INTO `db`.`data_id1_id2_id3` (pdate, id1, id2, id3, total) VALUES ($dt, $k1, $k2, $k3, $total) ON DUPLICATE KEY UPDATE total=total+$total;";
my $cSTH=$dbh->prepare($cSQL) || next;
$cSTH->execute() || next;
}
}
}

Before you ask, yes, the variable $dt is already quoted as well.

The structure of the table is:
+-------+--------------+------+-----+-------------------+-----------------------------+
| Field | Type | Null | Key | Default | Extra |
+-------+--------------+------+-----+-------------------+-----------------------------+
| pdate | timestamp | NO | PRI | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
| id1 | varchar(50) | NO | PRI | | |
| id2 | varchar(100) | NO | PRI | | |
| id3 | varchar(100) | NO | PRI | | |
| total | int(11) | YES | | NULL | |
+-------+--------------+------+-----+-------------------+-----------------------------+

Any advice?
Hi rbjolly,

To answer your question, it was accepting values until running into the brick wall and failing.

But I discovered it was a problem w/ my timestamp field. After resolving it, the script is working as normal.
 
Back
Top