SQL Server Query Help

Question

My problem is I am comparing two records from different tables and deleting those that match.

I have found this bit of code :

DELETE emails 
  FROM emails 
 INNER JOIN CustRecords ON CustRecords.Email = emails.email

Which works well, but i would also like to have a return value of how many records have been deleted.

Now if I do an SQL Query direct this is given to me but if I use my asp program it doesn't show a thing.

I know I have to use the count() funtion but I'm only still a beginner.

If possible i would like the count result as a variable like count = (how many records deleted)?

Answer 1

How about appending another query after your first?

DELETE emails FROM emails INNER JOIN CustRecords ON CustRecords.Email = emails.email ; SELECT @@rowcount AS 'RowsChanged'

Answer 2

Better method, assuming you're using an ASP database query:

You should note that the SqlCommand.ExecuteNonQuery() returns the number of rows affected as an integer. Like this (add try/catch'es, etc):

        SqlConnection con = new SqlConnection("...");
        SqlCommand cmd = new SqlCommand("DELETE emails ... ", con);
        cmd.CommandType = CommandType.Text;
        con.Open();

        int rowsAffected = cmd.ExecuteNonQuery(); //Note this line
        // ...

Answer 3

Ok i think i've managed to work this out... Seems to be that you can't use @@ROWCOUNT in an ASP line so i've got round the problem by using these 2 lines:

strSQL_count = "SELECT COUNT(email) AS 'count' FROM emails INNER JOIN CustRecords ON CustRecords.Email = emails.email"
Set rs = conn.Execute(strSQL_count)

count = rs("count")

strSQL = "DELETE emails FROM emails INNER JOIN CustRecords ON CustRecords.Email = emails.email"
Set rs = conn.Execute(strSQL)

I know that their is a simplier way of doing this but i'm only a beginner!!!

Thanks rlb.usa for you help

Answer 4

You could use the OUTPUT clause to send a list of identities that have been deleted to a table variable (or temp table) and then just select a count from that. That is, if you're using SQL Server 2005 or 2008. To best use this, i'd put it in a stored procedure so you can execute it and return the output, rather than having to mess around with multiple queries in your ASP code.

The code:

DECLARE @TempTable TABLE(ID INT)

DELETE Emails
OUTPUT deleted.ID INTO @TempTable
FROM Emails INNER JOIN CustRecords ON CustRecords.Emails = Emails.Emails

SELECT COUNT(*) AS DeletedRecords FROM @TempTable

I've tested this and it works, so it should work for you.