-1

I would like to delete the output of the below SELECT command but I am not a DBA and my SQL knowledge is rather elementary. I want to delete the users matching the SELECT statement.

SELECT count()
    ,u1.username
    ,u1.real_name
    ,group_concat(u1.STATUS)
    ,group_concat(u1.options)
    ,min(u1.end_date)
    ,max(u1.end_date)
FROM users u1
INNER JOIN (
    SELECT ui.username
        ,max(end_date) AS max_end_date
    FROM users ui
    GROUP BY ui.username
    ) u2
    ON (u1.username = u2.username)
        AND (
            (u1.end_date = max_end_date)
            OR u1.STATUS = 'locked'
            )
WHERE u1.username NOT IN (
        'someuser1'
        ,'someuser2'
        )
GROUP BY username
HAVING count() > 1
    AND group_concat(u1.STATUS) LIKE '%active%'
ORDER BY username;
  • What are you trying to delete? – Scott Hodgin May 9 at 10:41
  • What is coming from that SELECT statement. The rows matching the SELECT. I will edit my question to emphasize on that. Thank you. – Bluz May 9 at 10:47
  • Please edit to include the tags relevant to the database system you're using. – AakashM May 9 at 10:51
  • So, are you trying to delete from the Users table where the username comes out of this select query? – Scott Hodgin May 9 at 10:52
  • Yes Scott, that's correct. – Bluz May 9 at 10:55
-1
    DELETE FROM users u1 WHERE username IN
    (
        SELECT s1.username FROM
        (
            SELECT count()
                    ,u1.username
                    ,u1.real_name
                    ,group_concat(u1.STATUS)
                    ,group_concat(u1.options)
                    ,min(u1.end_date)
                    ,max(u1.end_date)
            FROM users u1
            INNER JOIN (
                    SELECT ui.username
                            ,max(end_date) AS max_end_date
                    FROM users ui
                    GROUP BY ui.username
                    ) u2
                    ON (u1.username = u2.username)
                            AND (
                                    (u1.end_date = max_end_date)
                                    OR u1.STATUS = 'locked'
                                    )
            WHERE u1.username NOT IN (
                            'someuser1'
                            ,'someuser2'
                            )
            GROUP BY username
            HAVING count() > 1
                    AND group_concat(u1.STATUS) LIKE '%active%'
            ORDER BY username
        )s1
    )

TRY THIS...

  • Answers that don't explain why they are appropriate are not well received. Can explain why this is bit of code is the correct answer for the question? – James Jenkins May 10 at 16:59

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.