Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 3 of 3
  1. #1
    Regular Coder
    Join Date
    Aug 2010
    Posts
    418
    Thanks
    18
    Thanked 2 Times in 2 Posts

    deleting duplicates

    I'm sure I had a version of this that worked. but now I get an error:
    Operand should contain 1 column...

    Here's what I'm trying:
    Code:
    DELETE FROM table WHERE plant_id IN (SELECT plant_id, term_id, high_low, male_female, COUNT(*) FROM termin_connect_to_plants HAVING COUNT(*) > 1 )  LIMIT 1

  • #2
    New Coder
    Join Date
    Oct 2013
    Posts
    12
    Thanks
    0
    Thanked 0 Times in 0 Posts
    IN() expects a set of values, your subquery is returning records with five fields.
    You are also not grouping so you will get extremely weird results.

    The LIMIT 1 will simply delete one record that matches the entire WHERE, not just the first of every duplicate.

    But, what defines a duplicate in your table?

  • #3
    Regular Coder
    Join Date
    Aug 2010
    Posts
    418
    Thanks
    18
    Thanked 2 Times in 2 Posts
    Right now I'm trying to clean up a bunch of duplicates so I can change the table to prevent further duplicates.

    I'm trying to find any items where the entire row matches (all 5 columns are same values) and delete all but one


  •  

    Posting Permissions

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