Portal Home > Knowledgebase > Articles Database > On Duplicate Key Delete
On Duplicate Key Delete
| Posted by aradapilot, 06-02-2009, 03:40 PM |
| Alright...I'm facing something that I've never seen before in years of mysql work. Basically, an app generates a query based on user preferences containing field names. The tricky bit is, I need to write a function that deletes a row without knowing what field is the key, as in different instances it would change. It can be related to an insert-on duplicate key update, which is how these rows are updated, where it provides all the fields and automatically updates the row that matches whatever the key is for that table.
I can't seem to get the delete part though. What I'd need is to provide a row to the server, and if it matches a row considered a duplicate based only on unique keys, it deletes that row. This all needs to be done without really knowing which fields are unique.
As an example, one of the tables stores user session info.
this one in itself shows many possibilities, it can be keyed on userid, ip_address,session_id...and the other fields may vary...i.e. keyed on userid (shown) allows the IP to change.
So...how do I delete my row? I'd rather not pull key info from the schema tables and loop through each possibility to format the delete query...this should be doable with one simple thing that is slipping my mind.
|
| Posted by phsource, 06-02-2009, 06:51 PM |
| Would this be possibly what you're looking for?
MySQL REPLACE statement
|
| Posted by foobic, 06-02-2009, 07:10 PM |
| A bit of an awkward way to do it, but perhaps you could add a new flag "todelete" to the table. Then you could insert your comparison data with todelete set and insert-on duplicate key update. Finally (or for periodic cleanup) delete from table where todelete = TRUE.
|
| Posted by aradapilot, 06-03-2009, 08:13 AM |
| phsource:
replace is close, but what replace does is match a row on whatever key, delete it, then put the new one in instead. I need it to only delete it, and leave nothing behind.
foobic:
that could work, except that this is a function that will be running on many different tables, and I can't guarantee that people using it will put that field in...the goal is to get a plug-and-play type function.
|
| Posted by aradapilot, 06-03-2009, 03:34 PM |
| here is, perhaps, a better example/explanation:
Say this row exists:
if i have data in the application that reads:
if this table is as shown keyed on session_id, the query I need to write would delete that row, as the row in the application would match as a duplicate. however, in the other version of that table, the key is on ip_address, and this row would not match anything and therefore nothing would happen.
That's my goal.
|
| Posted by tim2718281, 06-03-2009, 09:33 PM |
| Suppose there are two rows in the table already, one matching the application data on session_id, one matching on ip_address.
You need to delete one of the rows; but without additional information, you can't tell which one.
|
| Posted by horizon, 06-04-2009, 12:12 AM |
| The solution is, indeed, with the REPLACE statement if you wish to eliminate duplicated values. However, yes, there is a solution and this solution is by selecting each of these matching values and using a while loop statement in order to delete all identical values entirely from the table.
Although, if you'd like to keep the very last one of the session ID (and if the user's session is still active) you can always select the session ID and order by desc for the date with a limit of 1. Then, on your next select query, you could use the 'NOT IN' statement so that it would remove all the rest of the fields based on this session ID and keeping the last one relatively.
|
| Posted by aradapilot, 06-04-2009, 08:11 AM |
| tim: in one instance, the first would be deleted...in the second instance, the second would be deleted. the logic that was used was one always starts a session and keys on it, the other one does not always (or ever, all the session values are nulled currently, but I didn't write the app) and keys on the IP...not the best method, but I'm just hired on and they don't want to change it.
horizon: so you think i should REPLACE into the table with the array in the app, then delete with a where based on each of those fields? that's possible, but it's a delete, insert, and then delete running each time...I was hoping there would be a way to do this with only one action on the db.
|
| Posted by horizon, 06-04-2009, 10:07 AM |
| My last suggestion wasn't about a constant checkup. It is rather a simple fix of finding duplicated values in case there would be. The constant checkup should remain with the REPLACE statement. Normally, after the simple fix has been applied as mentioned above, there shouldn't be anymore duplicated values.
One thing I forgot to mention is that the SID should always be encrypted (try with md5 or with a for loop by generating each chains of encrypted IDs for your users). After, then, you will probably wonder how to remove expired sessions IDs ? Try with the date set by micro time 'or' by SQL date time fields.
After a particular time of inactivity, the script should remove expired sessions from your SQL sessions table or by stored cookies (if applied).
|
| Posted by aradapilot, 06-04-2009, 10:35 AM |
| ah, but the issue is not with duplicated rows...there are none. the issue is with finding a matching row to delete from an array of fields in php. i.e. if i were to insert the array as a row, it would fail on duplicate key error...i want the row making it fail to delete, but no row to be inserted in its place.
as for session id encryption, it's been suggested but shot down by my employer for this project. I'm leaving it as is. but there is a cron that clears out old timed out sessions.
|
| Posted by horizon, 06-04-2009, 10:53 AM |
| if i were to insert the array as a row, it would fail on duplicate key error
That is correct since the primary key is not auto incremented and should not be.
- i want the row making it fail to delete, but no row to be inserted in its place.
The REPLACE statement would be the perfect solution for this. However, if it wasn't applied before and your site has already gone live and the sessions table is not empty, I would recommend putting your store on maintenance in order to make the proper corrections to your sessions routine with the new SQL queries which would actually replace those instances to get rid of all new insertion if there are none to be inserted but rather replaced instead.
Aside note: Your session ID field's length is 255. I would also recommend to replace it to 32 and, if you're using int values with your session routine, to switch from varchar(32) to int(11).
|
| Posted by aradapilot, 06-04-2009, 10:57 AM |
| this is all in a dev environment, promotions to live site are done off hours. how do you get replace to stop after deleting and not put the new row in? that's a use of it I've never seen.
|
| Posted by horizon, 06-04-2009, 11:06 AM |
| The same concept of INSERT INTO but, instead, REPLACE INTO. As I said above, if there are already inserted values in your table and just looking for to replace instead of insert on the fly, this cannot be done without resetting the table if you're not currently clearing out expired sessions by inactive time which would be problematic if you're looking for to replace values instead of values right now.
|
| Posted by aradapilot, 06-04-2009, 11:10 AM |
| but in my experience, replace always inserts the new row where the old one was. I can't find any info that suggests an option to skip that?
|
| Posted by horizon, 06-04-2009, 11:38 AM |
| What REPLACE INTO is similar of deleting the row and updates it again on the same row (kind of reinserts the values on the same row). The idea is not to skip it but rather use it, since my first answer on this topic was about using it - not rejecting it.
|
| Posted by aradapilot, 06-04-2009, 12:15 PM |
| ah, yes. but the row must be deleted, the function i'm writing is a universal 'delete matching row' function
|
| Posted by horizon, 06-04-2009, 04:44 PM |
| If the row must be deleted and the site is already launched to the public, I'd recommend to put your site on maintenance and clear out all your sessions table in order to make the proper modifications (REPLACE INTO statement) into your routines so that this event does not happened again.
|
Add to Favourites 
Print this Article
Also Read
Shared Hosting
Shared Hosting
