Search code examples
sqlselectsql-server-2000sql-delete

Delete from select in sql server 2000?

I want to delete anything that is returned by this select query:

SELECT  u.*
FROM    (
        SELECT  userName, groupId, MAX(userId) AS maxId
        FROM    userTable
    where userName <> '' and userName is not null
        GROUP BY
                userName, groupId
        HAVING  COUNT(*) > 1
        ) q
JOIN    userTable u
ON      u.userName = q.userName
        AND u.groupId= q.groupId
        AND u.userId <> q.maxId)

How can I do this?

Solution

  • just replace SELECT u.* with DELETE U