Beefy Boxes and Bandwidth Generously Provided by pair Networks
Welcome to the Monastery

Comment on

( #3333=superdoc: print w/replies, xml ) Need Help??
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=?

In reply to Re: sql queries updation improper by graff
in thread sql queries updation improper by valavanp

Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post; it's "PerlMonks-approved HTML":

  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Titles consisting of a single word are discouraged, and in most cases are disallowed outright.
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.
  • Log In?

    What's my password?
    Create A New User
    and all is quiet...

    How do I use this? | Other CB clients
    Other Users?
    Others wandering the Monastery: (6)
    As of 2018-04-25 20:27 GMT
    Find Nodes?
      Voting Booth?