Um, if an auto-increment field is the primary key, and you are only inserting/updating non-key fields, how would you know whether or not a given set of fields is a "duplicate" of an existing row in the table?

I see that your insert fields and your update fields are the same set, except for "url". Perhaps "url" should be defined the primary key, instead of an auto-increment field. Without this, the "ON DUPLICATE KEY UPDATE" operation makes no sense and cannot be applied to the table.

You can make "url" the primary key while still having an auto-increment "row_num" field (or a "created_at" date/time field) that is not the primary key, and this can still be used to do the "order by" and "limit 10" that you want.

BTW, you really should be using placeholders for the values in the insert/update statement, and passing those variables as params to the "execute" call; if you use the same statement for more than one set of fields, and especially if the values are coming from outside your script (i.e. any "untrusted" source, like cgi params, environment variables, files, or other user input), placeholders are The Best Way.

In reply to Re: UPDATE ON DUPLICATE and sorting by graff
in thread UPDATE ON DUPLICATE and sorting by coldfingertips

Title:
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, details, 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, summary, 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.