1. Advertising
    y u no do it?

    Advertising (learn more)

    Advertise virtually anything here, with CPM banner ads, CPM email ads and CPC contextual links. You can target relevant areas of the site and show ads based on geographical location of the user if you wish.

    Starts at just $1 per CPM or $0.10 per CPC.

I want to remove records from another table that have those phone numbers in them

Discussion in 'MySQL' started by pmf123, Jul 3, 2017.

  1. #1
    I have a table with a list of phone numbers.

    I want to remove records from another table that have those phone numbers in them.

    Something like this, but I know this isn't correct:

    delete from db2013 where phone = (SELECT phone from db2010)
    SEMrush
     
    pmf123, Jul 3, 2017 IP
    SEMrush
  2. PoPSiCLe

    PoPSiCLe Illustrious Member

    Messages:
    4,622
    Likes Received:
    717
    Best Answers:
    151
    Trophy Points:
    420
    #2
    
    DELETE a
    FROM db2013 a
    INNERJOIN db2010 bON b.phone = a.phone
    
    Code (markup):
    I think this should work - however, you might need a filter condition, but I think this should work fine for your purpose
     
    PoPSiCLe, Jul 4, 2017 IP
  3. pmf123

    pmf123 Well-Known Member

    Messages:
    1,389
    Likes Received:
    69
    Best Answers:
    0
    Trophy Points:
    165
    #3
    Sorry doesn't work for me.... I appreciate your help

    ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'INNERJOIN db2010 bON b.phone = a.phone' at line 1
     
    pmf123, Jul 5, 2017 IP
  4. Blank ™

    Blank ™ Well-Known Member

    Messages:
    224
    Likes Received:
    18
    Best Answers:
    6
    Trophy Points:
    110
    #4
    Of course it doesn't. There's no such thing as "bON".
     
    Blank ™, Jul 5, 2017 IP
  5. PoPSiCLe

    PoPSiCLe Illustrious Member

    Messages:
    4,622
    Likes Received:
    717
    Best Answers:
    151
    Trophy Points:
    420
    #5
    Ah, my bad, missing space there. Change it to this and see if it works:
    
    DELETE a
    FROM db2013 a
    INNERJOIN db2010 b ON b.phone = a.phone
    
    Code (markup):
    However, it might not - I'm not sure if this works on MariaDB (although MySQL / MariaDB is mostly (98%) compatible)
     
    PoPSiCLe, Jul 5, 2017 IP
  6. pmf123

    pmf123 Well-Known Member

    Messages:
    1,389
    Likes Received:
    69
    Best Answers:
    0
    Trophy Points:
    165
    #6
    Looks like that is working, there are about 80 million records, so might be a while before I know for sure :cool:
     
    pmf123, Jul 6, 2017 IP
  7. qwikad.com

    qwikad.com Illustrious Member Affiliate Manager

    Messages:
    5,391
    Likes Received:
    1,005
    Best Answers:
    17
    Trophy Points:
    400
    #7
    80 mil records would be hard to delete, unless you do them in batches. Even if you do a million at a time you'll probably have issues. Were you able to delete some / all of them?
     
    qwikad.com, Jul 10, 2017 IP
  8. pmf123

    pmf123 Well-Known Member

    Messages:
    1,389
    Likes Received:
    69
    Best Answers:
    0
    Trophy Points:
    165
    #8
    Where would I add a limit in the query.

    Also if I did limit it, how could I do the next batch without going thru the old ones again?

    Is it as simple as adding this in the query somewhere?

    LIMIT 100000

    LIMIT 100001,100000
     
    pmf123, Jul 10, 2017 IP
  9. qwikad.com

    qwikad.com Illustrious Member Affiliate Manager

    Messages:
    5,391
    Likes Received:
    1,005
    Best Answers:
    17
    Trophy Points:
    400
    #9
    I am not good at any of that myself, @PoPSiCLe would tell you exactly how it should be done in that particular instance. I don't think LIMIT is the right thing to use in your case. It would be easy to do if the phone #'s had some kind of count. Let's say you have a column that's named "count", then you could do a clause: WHERE count >= '1' AND count <= '50000'
     
    qwikad.com, Jul 10, 2017 IP
  10. Blank ™

    Blank ™ Well-Known Member

    Messages:
    224
    Likes Received:
    18
    Best Answers:
    6
    Trophy Points:
    110