stored procedure creation

Question

Hi
I have created the stored procedure like this but i need the records generated by the following query should not be displayed and to be deleted

anyone plz help me

SELECT X.*
FROM MyTable X
INNER JOIN
(SELECT lastname, firstname
FROM MyTable
GROUP BY lastname, firstname
HAVING COUNT(*) > 1) Y
ON X.lastname = Y.lastname and X.firstname = Y.firstname
ORDER BY X.lastname, X.firstname

USE testdb;
GO
IF OBJECT_ID ( 'usp_getallrec', 'P' ) IS NOT NULL
DROP PROCEDURE usp_getallrec;
GO
CREATE PROCEDURE usp_getallrec
AS
SELECT lastname, firstname, salution, MAX(address)
FROM MyTable
GROUP BY lastname, firstname, salution
GO
SELECT X.*
FROM MyTable X
INNER JOIN
(SELECT lastname, firstname
FROM MyTable
GROUP BY lastname, firstname
HAVING COUNT(*) > 1) Y
ON X.lastname = Y.lastname and X.firstname = Y.firstname
ORDER BY X.lastname, X.firstname

Answer 1

Do you want to selected rows to delete the rows ? WOuld be nice if you could just describe your query or the intention in your question. I couldn´t know that from your explanation. Perhaps you could also post some DDL of your table, that we can see how to delete the rows that are "duplicate" (if that is your original problem)

HTH, Jens Suessmeyer.

---
http://www.sqlserver2005.de
---

Answer 2

I'm not sure I understand the question.  Why aren't you using the keyword "DELETE" as in DELETE FROM table WHERE condition?