sql queries updation improper

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

Hi monks, I have the following code to insert into mysql db. i am posting just the code which i have problem.

@color=(Blue, Silver);
$newpid=8000;
foreach $color(@color){
        $color=~s/^\s*//g;
        my $incolor_qry="insert into colorimage_info(pid,color) values
+ ('$newpid','$color')";
        my $incolor_qy=$db->prepare($incolor_qry);
        $incolor_qy->execute;
        }
[download]

After inserting this i need to update the rows which i inserted by the following code:

@color_image=(a1.jpg, b1.jpg);
foreach $color_image(@color_image){
        $color_image=~s/^\s*//g;
        my $upcolor_qry="update colorimage_info set color_image='$colo
+r_image.JPG' where id='$newpid';
        my $upcolor_qy=$db->prepare($upcolor_qry);
        $upcolor_qy->execute;
    }
[download]

The results which i am getting is

pid cid color  color_image
8000 1  Blue   a1.jpg
8000 2  Silver a1.jpg
[download]

The color_image column is not updated properly. I am missing some thing here. Can anyone tell me where i am wrong.cid column is autocremented and not null.

Comment on sql queries updation improper Select or Download Code

Replies are listed 'Best First'.
Re: sql queries updation improper by derby (Abbot) on Jul 15, 2007 at 15:30 UTC
Uhh ... yeah ... you're missing something. Read that second sql statement out loud while looking at your table. Do you see it? `update table set field = value where id = key`. Looks like the table is updated correctly to me. Still don't see it? Okay, here's a hint: where's cid come into play? -derby	[reply] [d/l]
Re^2: sql queries updation improper by Tobin Cataldo (Monk) on Jul 16, 2007 at 13:00 UTC
The OP said that cid is autoincremented. With MySQL you don't have to handle an autoincremented column.	[reply]
Re^3: sql queries updation improper by derby (Abbot) on Jul 16, 2007 at 13:58 UTC
Autoincrementing happens on the insert, not the update. There's not enough info in the OP but cid is either the primary key or part of a compound key and it needs to be part of the update sql. (sigh ... I need to remove master of subtlety from my CV). -derby	[reply]
Re^3: sql queries updation improper by spatterson (Pilgrim) on Jul 17, 2007 at 15:39 UTC
This is where DBI]'s last_insert_id comes in handy ... oh, thats only in DBD::Pg but you can always select the highest id immediately after inserting a record. just another cpan module author	[reply]
Re: sql queries updation improper by kyle (Abbot) on Jul 16, 2007 at 02:21 UTC
According to the table you show, every record has the same value for `pid`. As such, when you `UPDATE` ... `WHERE pid=8000`, every record is changed. Your program will set every `color_image` to some value, then set them all to some other value, etc. At the end of the loop, they'll all have the last value set. What you need to do is write the `WHERE` clause so that each `UPDATE` affects only one record (or at least only the records you want to affect).	[reply] [d/l] [select]
Re: sql queries updation improper by perllove (Beadle) on Jul 15, 2007 at 15:28 UTC
Is the set statement be like "set color" instead of set color_image? I mean I think you are updating a column which is not existing in the DB	[reply]
Re: sql queries updation improper by graff (Chancellor) on Jul 16, 2007 at 13:03 UTC
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 ); } ...` [download] 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=?` [download]	[reply] [d/l] [select]


Perl Monk, Perl Meditation
	PerlMonks