CODEWITHSUNDEEP
sql-server
newbieCSharp
newbieCSharp

Reputation: 191

SqlServer Update Case statement

I have a Staging and Master table with columns like this Step 1: 

Create table Staging(
    ID varchar(10) not null, 
    name varchar(100) not null, 
    createddate datetime not null
)

Step 2: 

insert into Staging('1','Amy','2014-01-01');

Step 3:

Create table Master(
    ID varchar(10) not null, 
    name varchar(100) not null, 
    createddate datetime not null,
    verifieddate datetime not null
)

Step 4: 

insert into master(
    select id,
            name,
            createddate,
            createddate 
    from    staging s 
    left outer join master on m on m.id=s.id where m.id is null);

Day 2: 

delete from Staging;
insert into Staging('1','Amy','2014-02-01');
insert into Staging('2','Binny','2014-01-01');

I run Step 4 that inserts ID=2 to Master. I want to update ID=1 by keeping the createddate same but verifieddate should be changed to 2014-02-01. So, I wrote update statement like this

update Master set VerifiedDate = 
(case when i.verifieddate < a.createddate then a.createddate end) 
      FROM Staging a inner join 
      Master i 
     on i.id=a.[ID] where i.verifieddate < a.createddate

It is not working. Any suggestions.

Upvotes: 0

Views: 53

Answers (1)

Rowland Shaw
Rowland Shaw

Reputation: 38130

In theory, you should be able to do it with an UPDATE statement like:

UPDATE m

SET         VerifiedDate = s.CreatedDate

FROM        Master AS m

INNER JOIN  Staging AS s
    ON      m.Id = s.Id
    AND     s.CreatedDate > m.VerifiedDate

Upvotes: 2

Related Questions