I don't think its possible in one query. One thing you can do is lock the table when you select the 20 rows and determine whether to do the insert. Then unlock when done. This avoids the concurrency issue you are having, but it may cause unacceptable perfomance if you have a lot of queries hitting this table. Maybe someone else has a better solution.
John A. McCaskey -----Original Message----- From: Grant Giddens [mailto:[EMAIL PROTECTED] Sent: Monday, June 21, 2004 4:59 PM To: [EMAIL PROTECTED] Subject: Insert data if not duplicate based on order Hi, I have a table that has many rows. I want to be able to insert a new row only if has a unique field of the last 20 rows when I sort them by date. Currently before I insert a new row, I will select a specific field for 20 rows and run a for loop in php looking for a match with the data I'm getting ready to insert. If a match occurs I will skip the insert command. This normally works fine, but I have seen occasions when 2 people visit my website at the same exact time. In this case, sometimes I might insert the data twice because I think I'm spinning in the for loop for 2 site visitors simultaneously. I know this is bad syntax, but this is what I imagine a insert query to look like: INSERT INTO $table (datax, datay, dataz) VALUES ('x', 'y', 'z') IF 'x' unique WHEN ordering by 'z' DESC LIMIT 20 In this example, 'z' is a datetime field and 'x' is a varchar. I don't mind duplicate entries in my table, but I don't want duplicates entries to occur within 20 rows when sorting by date. Is this possible to do with one INSERT command? I saw the IGNORE statment in the mysql manual, but I don't think it really aplies to what I'm trying to do here. Any suggestions would be appreciated, Thanks, Grant __________________________________________________ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
