Delete records from two tables based on one table's column value

[BOI-Guy]

Hello,
I have two tables aspnet_Membership and aspnet_Users. I want to remove all of the records from both tables that are older than 30 days by using a single stored procedure.

So far I have this:

BEGIN
	BEGIN TRY

		BEGIN TRANSACTION

		DELETE FROM aspnet_Membership
			WHERE CreateDate < DATEADD(DAY, -30, GETDATE())
		
                COMMIT TRAN	

	END TRY
	
	BEGIN CATCH
		IF @@TRANCOUNT > 0
			ROLLBACK TRAN
	END CATCH
END

but I cannot figure out how to also delete the same, or matching, records in the aspnet_Users table because is does not have a CreateDate column. Both tables do have a UserId column, can someone please show me how to do this?

-BOI-Guy

[sknake]

[edit] removed dangerous query [/edit]

[BOI-Guy]

Hi Scott,
Thanks for your suggestion. I tried your 2nd suggestion but it doesn't seem to delete any records.

[sknake]

Don't run that last query. It can delete your whole table -- I messed up. Give me a minute.

[sknake]

SET NOCOUNT ON

IF OBJECT_ID('TEST_Membership', 'U') IS NOT NULL DROP TABLE TEST_Membership
IF OBJECT_ID('TEST_User', 'U') IS NOT NULL DROP TABLE TEST_User
GO
Create Table TEST_Membership
(
  MembershipId int,
  UserId int,
  CreateDate datetime,
  Name varchar(30)
)
Create Table TEST_User
(
  UserId int,
  UserName varchar(30)
)
GO


Insert Into TEST_Membership (MembershipId, UserId, CreateDate, Name) Values (1, 10, GetDate()-90, 'Scott')
Insert Into TEST_Membership (MembershipId, UserId, CreateDate, Name) Values (1, 11, GetDate()-31, 'Frank')
Insert Into TEST_Membership (MembershipId, UserId, CreateDate, Name) Values (1, 12, GetDate()-10, 'Joe')

Insert Into TEST_User (UserId, UserName) Values (10, 'sknake')
Insert Into TEST_User (UserId, UserName) Values (11, 'frankie123')
Insert Into TEST_User (UserId, UserName) Values (12, 'joelong')

GO

Delete From TEST_User Where EXISTS (Select * From TEST_Membership (NOLOCK) Where TEST_Membership.UserId = TEST_User.UserId and CreateDate <= GetDate()-30)
PRINT 'Deleted: ' + Cast(@@ROWCOUNT as varchar)
Delete from TEST_Membership Where CreateDate < GetDate() - 30
PRINT 'Deleted: ' + Cast(@@ROWCOUNT as varchar)

Output (as expected/desired):

Deleted: 2
Deleted: 2

[BOI-Guy]

Thanks Scott,
I ran the code and it generates the following error:

The DELETE statement conflicted with the REFERENCE constraint "FK__aspnet_Me__UserI__15502E78".

It seems to want to do the delete on aspnet_Membership first. Any ideas?

[sknake]

..... you didn't mention you had FKs, thats another animal. Do you do cascading deletes?

[BOI-Guy]

These tables are created by Visual Studio IDE as part of the .Net CreateUserWizard control. How can I check to see if Cascading Deletes were used to create them?

[sknake]

run the query you have and see if the records disappear in both tables. I have no idea what to tell you about designer generated tables except they suck. Manually create your tables or else you'll never know what is going and you will wind up in situations like this PLUS it makes your database design inconsistent if it is authored by a human and a code generator.

DELETE FROM aspnet_Membership
			WHERE CreateDate < DATEADD(DAY, -30, GETDATE())

[BOI-Guy]

Okay thank you Scott. I don't really like generated "anything" but since I got stuck using the CreateUserWizard control, I had no choice but to go there. I guess the real question is how to do what I want when there are FKs thrown into the mix.