CODEWITHSUNDEEP
sqlsql-servert-sql
mr-dortaj
mr-dortaj

Reputation: 852

Delete record when update record in other table

I have two tables:

When use send the enter the activation code, I need to check the code in UA; if it exists, go to User and update column IsActive = true and when IsActive is true, go back to UserActivationCode and delete record of UserCode.

I wrote this code :

UPDATE [User] 
SET IsActive = 1 
FROM [User] U 
JOIN [UserActivationCode] UA ON U.Email = UA.Username 
WHERE UA.Username = @Username 
  AND UA.ActivationCode=  @ActivationCode 
  AND UA.ExpireTime < GETDATE() 
  AND U.IsActive = 0

but what should I change my code for back and delete record in UserActivationCode?

Upvotes: 0

Views: 117

Answers (4)

HABO
HABO

Reputation: 15816

The following code makes some assumptions since you didn't provide DDL for your tables. It wraps the work in a transaction to make the operation atomic (assuming a suitable transaction isolation level).

begin transaction;

declare @UserCodes as Table ( UserCode Int );

UPDATE [User] 
SET IsActive = 1 
-- Capture the   UserCode   of any affected rows.
output inserted.UserCode into @UserCodes
FROM [User] U 
JOIN [UserActivationCode] UA ON U.Email = UA.Username 
WHERE UA.Username = @Username 
  AND UA.ActivationCode = @ActivationCode 
  AND UA.ExpireTime < GETDATE() 
  AND U.IsActive = 0;

-- If any rows were updated then delete the corresponding   UserActivationCode   row(s).
delete from UserActivationCode 
  where UserCode in ( select UserCode from @UserCodes );

commit transaction;

Upvotes: 0

Sachintha Nayanajith
Sachintha Nayanajith

Reputation: 721

CASE 
WHEN EXISTS(SELECT 1 FROM UserActivationCode UA JOIN User U 
    ON UA.Username = U.Email
    WHERE UA.Username = @Username
    AND UA.ExpireTime < GETDATE() 
    AND U.IsActive = 0)
THEN
    UPDATE [User] 
    SET IsActive = 1 
    FROM [User] U JOIN [UserActivationCode] UA ON U.Email = UA.Username 
    WHERE UA.Username = @Username 
      AND UA.ActivationCode=  @ActivationCode 
      AND UA.ExpireTime < GETDATE() 
      AND U.IsActive = 

ELSE
    DELETE FROM [UserActivetionCode] WHERE  UA.ActivetionCode=  @ActivetionCode

Upvotes: 0

Vignesh Kumar A
Vignesh Kumar A

Reputation: 28403

You can use Merge statement here

DECLARE @T TABLE(Username VARCHAR(100));

MERGE [User] AS U
USING [UserActivetionCode] AS UA 
ON (U.Email = UA.Username AND UA.Username = @Username AND UA.ActivetionCode=  @ActivetionCode AND UA.ExpireTime < GETDATE() AND U.IsActive = 0) 
WHEN MATCHED
   THEN UPDATE SET U.IsActive = 1 
WHEN NOT MATCHED BY SOURCE AND 1 < 0
   THEN DELETE UA.Username INTO @T;


DELETE FROM [UserActivetionCode] WHERE UA.Username NOT IN(SELECT Username FROM @T)

Upvotes: 2

Ed Bangga
Ed Bangga

Reputation: 13006

you can add IF EXISTS condition you on your TSQL.

IF EXISTS(SELECT 1 FROM [User] t1
    JOIN [UserActivetionCode] t2 t1.Email = t2.Username 
    WHERE  UA.Username = @Username AND UA.ActivetionCode=  @ActivetionCode 
        AND UA.ExpireTime < GETDATE() AND U.IsActive = 0)
BEGIN
    UPDATE [User] SET IsActive=1 FROM [User] U JOIN [UserActivetionCode] UA ON 
    U.Email=UA.Username WHERE UA.Username = @Username AND UA.ActivetionCode=  @ActivetionCode 
    AND UA.ExpireTime < GETDATE() AND U.IsActive = 0

    DELETE FROM [UserActivetionCode] WHERE  UA.ActivetionCode=  @ActivetionCode 
END

Upvotes: 1

Related Questions