Case when else statement???

[zishan876]

hi I have the following:
The only thing is it would not update anything beyond SFB_COMP=1 ... is there something I am doing wrong here???

UPDATE sfdcprcbok_history_insert
       SET Pricebook2Id =
                 CASE
                   WHEN (SFB_COMP=1 AND SFB_PRCSTR=1 AND Name='Retail 1')
                                   THEN [Id]
                   WHEN (SFB_COMP=2 AND SFB_PRCSTR=1 AND Name='Retail 2')
                                   THEN [Id]
                   WHEN (SFB_COMP=3 AND SFB_PRCSTR=1 AND Name='Retail 3')
                                   THEN [Id]
                   WHEN (SFB_COMP=4 AND SFB_PRCSTR=1 AND Name='Retail 4')
                                   THEN [Id]
                   WHEN (SFB_COMP=6 AND SFB_PRCSTR=1 AND Name='Retail 6')
                                   THEN [Id]
                   WHEN (SFB_COMP=7 AND SFB_PRCSTR=1 AND Name='Retail 7')
                                   THEN [Id]
                   WHEN (SFB_COMP=8 AND SFB_PRCSTR=1 AND Name='Retail 8')
                                   THEN [Id]
                   WHEN (SFB_COMP=9 AND SFB_PRCSTR=1 AND Name='Retail 9')
                                   THEN [Id]
                   ELSE ''
                 END
,SFB_INSERT='N'
FROM sfdcprcbok_history_insert Z,SFDCPRCBOK_II C

 

[bborissov]

You didn't have ANY join between these tables
sfdcprcbok_history_insert and SFDCPRCBOK_II
If both have [ID] field you should point WHAT Id you want to use. Also if no condition is satisfied what you want to have in Pricebook2Id? I doubt you want to have empty string in it. So maybe, just maybe because i don't knwo what is your data and what exactly is your goal here that should be:

UPDATE sfdcprcbok_history_insert
       SET Pricebook2Id =
                 CASE
                   WHEN (SFB_COMP=1 AND SFB_PRCSTR=1 AND Name='Retail 1')
                                   THEN [Id]
                   WHEN (SFB_COMP=2 AND SFB_PRCSTR=1 AND Name='Retail 2')
                                   THEN [Id]
                   WHEN (SFB_COMP=3 AND SFB_PRCSTR=1 AND Name='Retail 3')
                                   THEN [Id]
                   WHEN (SFB_COMP=4 AND SFB_PRCSTR=1 AND Name='Retail 4')
                                   THEN [Id]
                   WHEN (SFB_COMP=6 AND SFB_PRCSTR=1 AND Name='Retail 6')
                                   THEN [Id]
                   WHEN (SFB_COMP=7 AND SFB_PRCSTR=1 AND Name='Retail 7')
                                   THEN [Id]
                   WHEN (SFB_COMP=8 AND SFB_PRCSTR=1 AND Name='Retail 8')
                                   THEN [Id]
                   WHEN (SFB_COMP=9 AND SFB_PRCSTR=1 AND Name='Retail 9')
                                   THEN [Id]
                   ELSE [b]Pricebook2Id[/b]
                 END
,SFB_INSERT='N'
FROM sfdcprcbok_history_insert Z
INNER JOIN SFDCPRCBOK_II C ON Z.?????? = C.?????
WHERE ??????

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.
Microsoft MVP VFP