Results 1 to 6 of 6
  1. #1
    Moderator
    Join Date
    April 6th, 2006
    Posts
    2,689
    Match then delete records in mySQL
    I'm posting this as I just found a simple solution to a very annoying problem.

    If you need to synchronize records across 2 tables, and want to match on some text field:

    Table 1 = current records
    Table 2 = all records

    Code:
    delete FROM table2 WHERE table2.textfield NOT IN (SELECT textfield FROM table1)
    Most of the answers I found were complex, and talked about joins, etc. - the "NOT IN" solution needed the select statement, which was the missing piece for me.

    The above should work like a charm.
    Last edited by teezone; June 9th, 2013 at 06:07 PM.

  2. #2
    Member
    Join Date
    May 20th, 2007
    Location
    USA
    Posts
    45
    Just to be safe I would always put an alias on table1:
    DELETE FROM table2 t2 WHERE t2.textfield NOT IN (SELECT t1.textfield FROM table1 t1)
    [url=http://www.DealSucker.com/]DealSucker.com[/url]

  3. #3
    The affiliate formerly known as ojmoo
    Join Date
    January 18th, 2005
    Posts
    1,466
    How long does that take to run? The join is not hard the whole key is that it is a left join

    delete from table2 left join table1 on table2.textfield=table1.textfield where table1.textfield is NULL.

    Its simple delete from table 2 which is joined to table 1 on key textfield where table 1 has a NULL value for that textfield

    Anyway I used select instead of delete since I needed my data ;-) But a database with 900,000 records that ended up deleting 86,000. My query took 0.0011 and your query took 0.0230. But either way its not a complex query to write or understand. You just have to know the two keys to it, the first is its a left join, won't work any other way and second you have to compare with NULL which is different than "" in this case anyway.
    Expert who says Moo

    a.k.a. OJMOO

    Cow Dance


  4. #4
    Moderator
    Join Date
    April 6th, 2006
    Posts
    2,689
    To be honest, it was the "join" concept that confused me (and still does!).

    These were two unrelated tables that simply shared an identifier - my goal was to delete (or select, as the case may be!) old records that didn't match the current primary table.

    I'm not an expert in mySQL and just needed to clean up the table so speed didn't matter - finding "not in" was a godsend.. perhaps not the most efficient, but it did the trick! I posted it on the off-chance someone else needs a basic solution

  5. #5
    The affiliate formerly known as ojmoo
    Join Date
    January 18th, 2005
    Posts
    1,466
    I had never written this query before but I understand you confusion. Join joins stuff, but its the stuff that isn't joined then you want to remove. The left join says ok, even if there isn't a value to join with, let the record show up with a result (lets not get into what the right join does, the guide says don't use it anyway)

    With the left join in place (and I know it gave the same results with select so it works) Lets use 3 feilds to show what is happening graphically with a select not a delete

    Table A key, Table B Key Table A Value (extra field)
    1111 1111 Happy
    1112 1112 Birthday
    1113 To
    1114 1114 You
    1115 Tee
    1116 1116 Zone


    You want to delete the records from table A who don't have an identical key in table B. Without the left join the records you want 1113 and 1115 won't be included in the join, because joins join stiff and if there wasn't anything to join it ignores it. But left says ok, if you have a value on left whether it has a corresponding one on Table B display it. So the ones you want to display (delete) are the ones with a NULL in table B i.e. doesn't exist. Simple.

    Anyway, assuming that your databases are big and you use proper indecies (indecies are very important in databases) this is much faster. The not in didn't take as long as I thought it would, but you never know sometimes these things take a while and if you had to do it alot it would slow down all your queries not just this one.

    Don't forget to optimize your databases on occasion but not too often, that slows queries more than anything else as the database can't be queried well while it is in flux.

    I am curious what complex queries were you being told?
    Expert who says Moo

    a.k.a. OJMOO

    Cow Dance


  6. #6
    Moderator
    Join Date
    April 6th, 2006
    Posts
    2,689
    I understand where you're coming from, appreciate the suggestion.. and yes, I know all about indices, my tables are optimized.

    Table A has been built over time, using records from Table B (your example would never happen). If value from Table A is 'NOT IN' the corresponding field from Table B, delete it. That's all I want - join adds a unnecessary layer, and I find it more confusing to interpret examples and apply them to my own database.

    I no longer have a developer working with me, so I'm left to my own devices to find simple solutions. mySQL is a specialty unto itself and if I had more time, I would enjoy learning more about optimizing queries, etc. I just had a table that had grown unwieldy and needed a quick clean-up - the query I presented will run once a month as a maintenance routine.

  7. Newsletter Signup

+ Reply to Thread

Similar Threads

  1. Understanding 'broad match' vs. 'exact match'
    By steve45 in forum Search Engine Optimization
    Replies: 3
    Last Post: June 3rd, 2011, 02:41 AM
  2. Delete records from the database using Date()
    By akinak in forum Programming / Datafeeds / Tools
    Replies: 0
    Last Post: February 5th, 2009, 05:50 PM
  3. Sorry, your find parameters did not match any records in our system.
    By La_Valette in forum Google Affiliate Network - GAN
    Replies: 3
    Last Post: November 22nd, 2006, 05:15 PM
  4. Question on Match Function of MySql
    By UKJ in forum Programming / Datafeeds / Tools
    Replies: 5
    Last Post: September 18th, 2005, 12:28 AM

Posting Permissions

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