CODEWITHSUNDEEP
sql-servert-sql
navig8tr
navig8tr

Reputation: 1854

Conditional Set in Update statement without a cursor

The following loop conditionally sets @EndDate based on the existence of other records in the same table. Basically, we only want to assign the current date to @EndDate if at least one record exists for the given ReferenceTypeId and each PersonId, KeepId and RemoveId.

How can I write this Update statement without a loop. 

    DECLARE ReferenceType_Cursor CURSOR FOR  
    SELECT ID FROM CORE.PersonExternalReferenceType 
    OPEN ReferenceType_Cursor   
    FETCH NEXT FROM ReferenceType_Cursor INTO @RefTypeId   

    WHILE @@FETCH_STATUS = 0 
    BEGIN
        SET @ENDDATE = NULL

        IF( EXISTS (SELECT 1 FROM CORE.PersonExternalReferences WHERE ReferenceTypeId = @RefTypeId AND PersonId = @KeepId) AND 
            EXISTS (SELECT 1 FROM CORE.PersonExternalReferences WHERE ReferenceTypeId = @RefTypeId AND PersonId = @RemoveId))
        BEGIN
            SET @ENDDATE     = @CURRENTDATE
        END

        UPDATE CORE.PersonExternalReferences  
        SET  
            PersonId        = @KeepID,
            ModifiedBy      = @USERNAME,
            ModifiedDate    = @CURRENTDATE,
            StartDate       = COALESCE(StartDate,CreatedDate,@STARTDATE),
            EndDate         =  @ENDDATE
        WHERE ReferenceTypeId = @RefTypeId AND PersonId = @RemoveID

    FETCH NEXT FROM ReferenceType_Cursor INTO @RefTypeId
    END
    CLOSE ReferenceType_Cursor   
    DEALLOCATE ReferenceType_Cursor

Upvotes: 1

Views: 53

Answers (3)

John Rees
John Rees

Reputation: 1813

So just to clarify your requirement:

You want to update every PersonExternalReferences where PersonId = @RemoveID.

For each of those records, check the existence of another PersonExternalReferences with the same ReferenceTypeId and PersonId = @KeepId. If one exists then set EndDate = @ENDDATE otherwise set EndDate = null.

To make sure it is working code, I've created a simplified ready to run example.

The schema set up is:

create table PersonExternalReferenceType (
  id varchar(100)
)
go

create table PersonExternalReferences (
  PersonId int,
  ReferenceTypeId varchar(100),
  ModifiedDate datetime,    
  EndDate datetime         
)
go

insert PersonExternalReferenceType
values ('foo'),('bar')
;

insert PersonExternalReferences
values
(1,'foo',null,null),
(2,'foo',null,null),
(3,'foo',null,null),
(4,'bar',null,null)
;

The actual update statement is

declare @KeepId int   = 1;
declare @RemoveId int = 2;

update PersonExternalReferences
set ModifiedDate = getdate(),
  EndDate = case when exists (
    select 1 from PersonExternalReferences as other
    where other.PersonId = @KeepId 
    and other.ReferenceTypeId = PersonExternalReferences.ReferenceTypeId
  ) then getdate() else null end
where PersonId = @RemoveId;

And then run again using 

declare @KeepId int   = 3;
declare @RemoveId int = 4;

And the final result is 

+----------+-----------------+-------------------------+-------------------------+
| PersonId | ReferenceTypeId |      ModifiedDate       |         EndDate         |
+----------+-----------------+-------------------------+-------------------------+
|        1 | foo             | NULL                    | NULL                    |
|        2 | foo             | 2016-03-03 10:27:40.507 | 2016-03-03 10:27:40.507 |
|        3 | foo             | NULL                    | NULL                    |
|        4 | bar             | 2016-03-03 10:27:40.517 | NULL                    |
+----------+-----------------+-------------------------+-------------------------+

Upvotes: 1

sam
sam

Reputation: 1304

I have two solutions for you. You just need to edit partition columns and where columns as per your need:

update a
set a.enddate = @enddate
from abc as a
where ReferenceTypeId = @RefTypeId AND PersonId = @RemoveID and
exists(select 1 from abc as b where b.ReferenceTypeId  = a.ReferenceTypeId  group by ReferenceTypeId,PersonId,KeepId,RemoveId  having count(*) >1)

and another one:

update a
set a.enddate = @enddate
from
(
select *, count(*) over (partition by ReferenceTypeId,PersonId,KeepId,RemoveId) as rn
from abc
) as a
where a.rn > 1 and ReferenceTypeId = @RefTypeId AND PersonId = @RemoveID

Let me know if this helps..

Upvotes: 0

RAY
RAY

Reputation: 2309

@CURRENTDATE 

 UPDATE CORE.PersonExternalReferences  
        SET  
            PersonId        = @KeepID,
            ModifiedBy      = @USERNAME,
            ModifiedDate    = @CURRENTDATE,
            StartDate       = COALESCE(StartDate,CreatedDate,@STARTDATE),
            EndDate         =  
case when (ReferenceTypeId = @RefTypeId 
AND PersonId = @KeepId  
and PersonID = @RemoveId) then 
 @CURRENTDATE 
else null end 
    /*Then you don't need to filter at all if you wanna loop trought whole table...   WHERE ReferenceTypeId = @RefTypeId AND PersonId = @RemoveID
*/

Upvotes: 0

Related Questions