Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 358
  • Last Modified:

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
0
netadmin2004
Asked:
netadmin2004
2 Solutions
 
Ephraim WangoyaCommented:
When testing for NULL, you can only use "IS" and "IS NOT"
0
 
netadmin2004Author Commented:
thanks. that's how i have it just added that in for future info. see anything wrong with the rest?
0
 
BartVxCommented:
Hi netadmin,

 i see two problems in your query:

the ',' after your table name should be removed. (opportunity_bak,)

each field should be set individually

0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
_agx_Commented:
(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.
0
 
netadmin2004Author Commented:
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'
)
0
 
netadmin2004Author Commented:
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'
)
0
 
jogosCommented:
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

0
 
netadmin2004Author Commented:
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.
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now