#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2013
    Posts
    15
    Rep Power
    0

    Deleting, Sql, Having


    hi,
    Iam trying to delete the result of:

    PHP Code:
    SELECT FROM Germany GROUP BY email HAVING count(email) > 1
    I tried:
    PHP Code:
    DELETE FROM Germany GROUP BY email HAVING count(email) > 1
    But it doesnt work...

    How can I do this?
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2012
    Posts
    194
    Rep Power
    77
    Originally Posted by computeruser13
    hi,
    Iam trying to delete the result of:

    PHP Code:
    SELECT FROM Germany GROUP BY email HAVING count(email) > 1
    I tried:
    PHP Code:
    DELETE FROM Germany GROUP BY email HAVING count(email) > 1
    But it doesnt work...

    How can I do this?
    Your missing 1 thing, compare your first SQL query with your second and you will see it
  4. #3
  5. --
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Jul 2012
    Posts
    3,959
    Rep Power
    1014
    Hi,

    both queries are conceptually wrong.

    When you group by the email column, you cannot select any other column, because its value simply isn't determined (unless it functionally depends on the email address). Say you have this table:

    Code:
    state                       email
    -------------------------------------------
    Nordrhein-Westfalen         foo@example.com
    Hessen                      foo@example.com
    Baden-Württemberg           foo@example.com
    Berlin                      foo@example.com
    Bayern                      foo@example.com
    When you do your query, what value would state contain for the "foo@example.com" group? "Nordrhein-Westfalen"? "Hessen"? "Baden-Württemberg"? Nobody knows. There is no definite value.

    Unfortunately, MySQL let's you get away with this gross mistake and picks one arbitrary value. So many people will never realize that their queries are wrong. But they are. When someday you switch to a real database system like PostgreSQL, Oracle or whatever, you cannot do stuff like that. Your screen will be full of error messages. The same is true if you turn on "strict mode" in MySQL. So I strongly suggest that you get away from this bad technique and write proper SQL.

    As a rule of thumb: When you do grouping, the SELECT clause can only contain aggregate expressions (SUM(), COUNT(), AVG(), ...) and columns that are in the GROUP BY clause. Other database systems are more sophisticated and will also let you select columns that functionally depend on the ones in the GROUP BY clause. But in MySQL, that's not possible.

    The DELETE query is also wrong, because you always delete particular datasets. You can tell MySQL to delete all datasets with a certain email address, but you cannot just write down some query with a GROUP BY clause. What should MySQL do with that? How does it know the datasets it's supposed to delete?

    You need to use the multi-table syntax of DELETE and do a join:

    Code:
    DELETE
    FROM
    	Germany
    USING
    	Germany
    	JOIN (
    		SELECT
    			email
    		FROM
    			Germany
    		GROUP BY
    			email
    		HAVING
    			COUNT(*) > 1
    	) AS multiple_emails ON Germany.email = multiple_emails.email
    ;
    The 6 worst sins of securityHow to (properly) access a MySQL database with PHP

    Why can’t I use certain words like "drop" as part of my Security Question answers?
    There are certain words used by hackers to try to gain access to systems and manipulate data; therefore, the following words are restricted: "select," "delete," "update," "insert," "drop" and "null".
  6. #4
  7. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2013
    Posts
    15
    Rep Power
    0
    is there a faster way to delete these rows ?(the statement is running about 5 hours)...
  8. #5
  9. No Profile Picture
    Contributing User
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Dec 2004
    Posts
    2,989
    Rep Power
    375
    go and post in the mysql database forum, they will have DB experts

IMN logo majestic logo threadwatch logo seochat tools logo