Re: UPDATE ON DUPLICATE and sorting

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.

Comment on Re: UPDATE ON DUPLICATE and sorting

Replies are listed 'Best First'.
Re^2: UPDATE ON DUPLICATE and sorting by coldfingertips (Pilgrim) on Feb 06, 2006 at 04:52 UTC
I know I can't do an update on duplicate with an auto increment, that was the main question. I am using the auto id to get the last 10. I would change the primary key to "url" because that's what I need to check duplicates for. The thing is, I believe I tried to change the primary key to "url" already today with the auto increment. It errored out saying I can't have that and a primary key (or some error like that). So I think an auto_increment has to be the primary key.	[reply]
Re^3: UPDATE ON DUPLICATE and sorting by graff (Chancellor) on Feb 06, 2006 at 05:14 UTC
Okay, it turns out that mysql does not allow you to declare an "auto_increment" field unless it's going to be the primary key. So don't use an auto_increment field. Use a date_time field instead (call it something like "create_date"), and whenever a new row is inserted, set its value with the "NOW()" function. Then you'll be able to "order by create_date desc limit 10". You don't need an auto_increment field -- you just need "url" to be the primary key, and you need something chronological to sort on. Update: About the 2nd part of your initial question: you could try to work out a two-stage query: find the "create_date" value for the row that is currently 10 rows "back" from the most recent row, then select all rows where create_date is greater than that value, and order by "Total". But I think it would be a lot easier to do a single query ordered chronologically, store those into an AoA or AoH and let the perl script sort them based on "Total".	[reply]