Re: sql queries updation improper

[graff]

You did not show us how the "cid" field is being set, and based on what you have shown, it's not clear why you need to do inserts and then updates on the same table. Why not assemble all the information first, and then just do inserts?

And at that point, if you are using a typical RDBMS like mysql or postgres or oracle, why not put the full-row insertion data into a plain tab-delimited file, and use the appropriate loading tool (mysqlimport or a "LOAD DATA INFILE" statement or sqlload or whatever). If/when you have many thousands of records to insert, this will go significantly faster.

But if you want to stick with DBI insert (and update?) statements like you've shown, you should at least learn about placeholders:

...
my $insert_clr_sql = "insert into colorimage_info (pid,color) values (
+?,?)";
my $insert_clr_sth = $db->prepare( $insert_clr_sql );

for my $color ( @colors ) {
    $insert_clr_sth->execute( $npid, $color );
}
...

update: Sorry, I just noticed your remark about how the "cid" field is an auto-increment (presumably "primary key") field. There are ways to get back the value of such a field from DBI after doing an insert (look for "last_insert_id" in the DBI man page), and you can then use that in later updates. Otherwise, you'll need to keep track of the relationship between "colors" ("Blue", "Silver") and "images" ("a1.jpg", "a2.jpg"), so that your update statement looks like:

update colorimage_info set color_image=? where pid=? and color=?