asked on

SQL Update Multiple Columns

I'm not sure what I'm getting wrong in this statement. Just assume the FROM is correct and below is where the errors are. In the first case I've used '=' and '<>' and the others I have 'is' and 'is not'. Not sure which is better to use or if one can't be used. Also, if there is a way to code this better that works too.

UPDATE opportunity_bak,

SET o.Stage_1_Date__c, o.Stage_2_Date__c, o.Stage_3_Date__c, o.Stage_4_Date__c, o.Stage_5_Date__c, o.Stage_6_Date__c =
      CASE
            WHEN o.Stage_1_Date__c = null AND o.Stage_2_Date__c = null AND o.Stage_3_Date__c = null AND o.Stage_4_Date__c = null AND o.Stage_5_Date__c = null AND o.Stage_6_Date__c = null
                  AND o.Stage_7_Date__c <> null
                  THEN '01/08/2011'
                  ELSE o.Stage_1_Date__c
            END,

SET o.Stage_1_Date__c, o.Stage_2_Date__c, o.Stage_3_Date__c, o.Stage_4_Date__c, o.Stage_5_Date__c =
      CASE
            WHEN o.Stage_1_Date__c is null AND o.Stage_2_Date__c is null AND o.Stage_3_Date__c is null AND o.Stage_4_Date__c is null AND o.Stage_5_Date__c is null AND o.Stage_6_Date__c is not null
                  THEN '01/08/2011'
            END,

SET o.Stage_1_Date__c, o.Stage_2_Date__c, o.Stage_3_Date__c, o.Stage_4_Date__c =
      CASE
            WHEN o.Stage_1_Date__c is null AND o.Stage_2_Date__c is null AND o.Stage_3_Date__c is null AND o.Stage_4_Date__c is null AND o.Stage_5_Date__c is not null
                  THEN '01/08/2011'
            END,

SET o.Stage_1_Date__c, o.Stage_2_Date__c, o.Stage_3_Date__c =
      CASE
            WHEN o.Stage_1_Date__c is null AND o.Stage_2_Date__c is null AND o.Stage_3_Date__c is null AND o.Stage_4_Date__c is not null
                  THEN '01/08/2011'
            END,

SET o.Stage_1_Date__c, o.Stage_2_Date__c =
      CASE
            WHEN o.Stage_1_Date__c is null AND o.Stage_2_Date__c is null AND o.Stage_3_Date__c is not null
                  THEN '01/08/2011'
            END,

SET      o.Stage_1_Date__c =
      CASE
            WHEN o.Stage_1_Date__c is null AND o.Stage_2_Date__c is not null
                  then '01/08/2011'
            END

ASKER CERTIFIED SOLUTION

Ephraim Wangoya

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

netadmin2004

ASKER

thanks. that's how i have it just added that in for future info. see anything wrong with the rest?

SOLUTION

BartVx

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

_agx_

(not for points...)

> When testing for NULL, you can only use "IS" and "IS NOT"

Technically it depends on your db settings. But I agree that's the default and 99% of the time you should use IS NULL, not = NULL.

netadmin2004

ASKER

Ok, I guess just getting one update to work to start with will work better. I have included everything this time. The problem I have is there are records that have 7 stages each. When they are in any stage higher than 1, that field and the ones before it should have the same date. However, some have a single value in stage 2 - 7 and nothing in the rest. So I want to find where a account has no value in 1 but does have a single value in any stage higher. And if so to populate all stages before it with the same value that is found in the stage higher of it. I do understand that it seems that it will be 6 different updates and not one big one. Hope I got that understandable.

UPDATE opportunity_bak

SET o.Stage_1_Date__c, o.Stage_2_Date__c, o.Stage_3_Date__c, o.Stage_4_Date__c, o.Stage_5_Date__c, o.Stage_6_Date__c =
      CASE
            WHEN (o.Stage_1_Date__c is null AND o.Stage_2_Date__c is null AND o.Stage_3_Date__c is null AND o.Stage_4_Date__c is null AND o.Stage_5_Date__c is null AND o.Stage_6_Date__c is null
                  AND o.Stage_7_Date__c is not null)
                  THEN '01/08/2011'
                  ELSE o.Stage_1_Date__c
            END

from opportunity_bak o
inner join "User" u on o.OwnerId = u.Id
inner join UserRole ur on u.UserRoleId = ur.Id
where o.Feed_Source__c like '%Data%' and
ur.Name in
(
'Community New Sales',
'Community New Sales-Central',
'Community New Sales-East'
)

netadmin2004

ASKER

What I'm thinking is this is a 6 part update. Starting at stage 7 to check if not null and if stage 6 is then stage 6 equals stage 7. and do that update 6 times, 6 for 5, 5 for 4, 4 for 3, 3 for 2, 2 for 1 and ending with that. what am i missing now:

UPDATE dbo.oppportunity_bak o

SET      o.Stage_1_Date__c =
      CASE
            WHEN o.Stage_1_Date__c is null AND o.Stage_2_Date__c is not null
                  then '01/08/2011'
            END

from dbo.oppportunity_bak o
inner join "User" u on o.OwnerId = u.Id
inner join UserRole ur on u.UserRoleId = ur.Id
where o.Feed_Source__c like '%saleslogix%' and
ur.Name in
(
'Community Banking New Sales',
'Community Banking New Sales-Central',
'Community Banking New Sales-East'
)

jogos

I think COALESCE solves your problem http://msdn.microsoft.com/en-us/library/ms190349.aspx

And you don't need to repeat SET

UPDATE opportunity_bak
SET o.Stage_1_Date__c
=coalesce(o.Stage_1_Date__c, o.Stage_2_Date__c, o.Stage_3_Date__c
, o.Stage_4_Date__c, o.Stage_5_Date__c, o.Stage_6_Date__c
, o.Stage_7_Date__c , '01/08/2011'
),
 o.Stage_2_Date__c
=coalesce( o.Stage_2_Date__c, o.Stage_3_Date__c
, o.Stage_4_Date__c, o.Stage_5_Date__c, o.Stage_6_Date__c
, o.Stage_7_Date__c , '01/08/2011'
),
.....

Open in new window

netadmin2004

ASKER

i got it:

UPDATE o
SET
o.Stage_1_Date__c = o.Stage_3_Date__c,
o.Stage_2_Date__c = o.Stage_3_Date__c

FROM oppportunity_bak o
where o.Stage_3_Date__c is not null and
o.Stage_1_Date__c is null AND o.Stage_2_Date__c is null

i just increase the stage number and start from the last stage and work my way down. i can now check for values before the update changes null values. works great too. 6 updates to run and im still not sure if there was some kind of way to work this into a loop.