Results 1 to 13 of 13
  1. #1
    Join Date
    Feb 2009
    Location
    Rutherford, NJ
    Posts
    9

    Unanswered: On Duplicate Key Delete

    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.
    Code:
    CREATE TABLE `sessions` (
      `userid` int(10) unsigned NOT NULL default '0',
      `username` varchar(255) NOT NULL default '',
      `password` varchar(255) default NULL,
      `session_id` varchar(255) default NULL,
      `ip_address` int(10) unsigned default NULL,
      `hostname` varchar(255) default NULL,
      `browser_agent` varchar(255) default NULL,
      `switch` tinyint(1) default NULL,
      `timeout` timestamp NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
      `data` varchar(60000) default NULL,
      PRIMARY KEY  (`userid`)
    ) ENGINE=MEMORY DEFAULT CHARSET=latin1
    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.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    let me see if i understand what you want

    there's a table, with an unspecified key, and you're looking at the following scenario...

    you are about to insert a row, but you want to do some sort of check to see if there's already a row with the same value for the unspecified key in the table...

    ... and this is where you lost me

    you want to delete the row that's already there, and then what? insert the new one, or not insert it either?

    i ahve a feeling you're gonna have to do this with application logic
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Feb 2009
    Location
    Rutherford, NJ
    Posts
    9
    I have a row that I want to compare to the database on an unspecified key, and delete a matching row if it exists. I can't just do a where on all fields, as whatever is not the key is not guaranteed to match. One way I thought is to run it as two statements, one is as a replace, the second deletes on all fields...but thats a sketchy workaround, and I don't want to make this any longer than necessary as it could run hundreds of times per second on different tables...

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    sorry, this makes no sense to me at all

    good luck with your project
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Feb 2009
    Location
    Rutherford, NJ
    Posts
    9
    ah...well thanks for trying. I'll see if anyone else has a solution

  6. #6
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    perhaps you can give us some sample data and a few examples...
    George
    Home | Blog

  7. #7
    Join Date
    Feb 2009
    Location
    Rutherford, NJ
    Posts
    9
    this is the simplest scenario it's for as yet:

    Code:
    CREATE TABLE `sessions` (
      `session_id` varchar(255) default NULL,
      `ip_address` int(10) unsigned default NULL,
      `switch` tinyint(1) default NULL,
      `timeout` timestamp NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
      PRIMARY KEY  (`session_id`)
    ) ENGINE=MEMORY DEFAULT CHARSET=latin1
    Say this row exists:

    Code:
    f19fa51716d13825319ceefdd67a9328    3636710205    1    2009-06-03 11:39:13
    if i have data in the application that reads:

    PHP Code:
    array(
    $keys[0]=>'f19fa51716d13825319ceefdd67a9328',
    $keys[1]=>3512437100,
    $keys[2]=>1,
    $keys[3]='2009-06-03 12:42:29'

    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.

  8. #8
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    Questions :
    • Why would you have 2 versions of the same table?
    • Why would the keys be different on both?
    • Why do you need to work out what the PK is on the fly?
    • Why is the PHP array index numerically rather than by name ie
      $keys["ip_address"]=>3512437100
    • That doesn't look like an ip address to me!
    • If these actions are caused by people using a screen then should you be worried about 100's of transactions per second?

    Thoughts :
    • Could you change the app or DB design so you don't get duplicate inserts?
    • Why not just write a simple stored proc for each table so that it takes the various parameters required by that table and it knows what the PK is so it can delete the original record incase of a duplicate insert.

  9. #9
    Join Date
    Feb 2009
    Location
    Rutherford, NJ
    Posts
    9
    Answers:
    1 & 2: this code is used accross multiple databases, each having their own. it will actually be for more varied tables, i just found those two only having one difference to be an easier example.
    3. And it doesn't need to be the pk, any unique key match will do. the app never needs to know what field is the key, it just needs to execute something based on a match with it, like replace, insert ignore, and insert on duplicate key update do...but in this case i need a delete.
    4. as above, because the app doesn't know the table setup. doing it that way would involve writing a separate function for each of dozens of tables, and a new one whenever one is added. they're pulled from a user preference table that is updated automatically. $keys[n] would be "ip_address", and as in that array, be the key of the value.
    5. it's a decimal representation of an IP, much smaller to store than as a string, check out INET_ATON()
    6. it'll be called on multiple tables per page load on multiple customer instances of a relatively high traffic SaaS application. that adds up fast during business hours.

    the issue isn't with having duplicate data...that's prevented by the keys (and the app) to begin with. it's how to identify the row based on a possibly-different-except-the-keys array to get rid of it (or possibly do other things, right now I just need to add delete functionality).
    and as for a stored proc, again it becomes a scale issue...writing one thing for each table, no way to add it dynamically, etc. this has to be adaptable to many tables.

    hope that answers you

  10. #10
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    What in depth answers and thanks for info on INET_ATON() - I'd never heard of that before!

    I guess your only solution is to pull the unique keys of each table when the app initialises (not difficult). Then build the SQL to do the delete (before an insert) from this. Obviously compiling and running 100's of SQL statements a sec is going to affect your database. You could look into batching the SQL from the different sites so your db doesn't get overloaded. That's assuming it doesn't matter if there's a slight delay between the SQL being sent and the SQL being actioned.

  11. #11
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by mike_bike_kite
    That doesn't look like an ip address to me!
    try going here: http://1158948195/

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  12. #12
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    Quote Originally Posted by r937
    try going here: http://1158948195/

    Oddly enough even the number by itself (without the http:// bit) is enough to work. You need to be fairly brave to click such a link though - I certainly didn't use IE - but I was faintly disappointed it wasn't a porn site.

  13. #13
    Join Date
    Feb 2009
    Location
    Rutherford, NJ
    Posts
    9
    well, you learn something new every day, hell I've been designing and managing mysql databases for over five years now and this is the first time the situation referenced by this thread has come up.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •