Updates Using Case

Question

Post reply

Updates Using Case

Nishernd

SSChasing Mays

Points: 611
More actions
April 22, 2008 at 3:27 am

#120974

update yyyy
case
when CLUSTER_CODE in ('1M' , '2M' , '3M' , '4M' , '1T') then TGI_CLUSTER_CODE = '01'
when CLUSTER_CODE in ('5M' , '6M' , '7M' , '2T' , '3T') then TGI_CLUSTER_CODE = '02'
when CLUSTER_CODE in ('8M' , '9M' , '4T' ) then TGI_CLUSTER_CODE = '03'
when CLUSTER_CODE in ('10M', '11M', '5T' ) then TGI_CLUSTER_CODE = '04'
when CLUSTER_CODE in ('12M', '13M', '14M', '6T' ) then TGI_CLUSTER_CODE = '05'
when CLUSTER_CODE in ('15M', '16M', '17M', '7T' ) then TGI_CLUSTER_CODE = '06'
when CLUSTER_CODE in ('18M', '19M', '20M', '8T' ) then TGI_CLUSTER_CODE = '07'
when CLUSTER_CODE in ('21M', '22M', '23M', '24M') then TGI_CLUSTER_CODE = '08'
when CLUSTER_CODE in ('25M', '26M', '9T' ) then TGI_CLUSTER_CODE = '09'
when CLUSTER_CODE in ('27M', '28M', '10T') then TGI_CLUSTER_CODE = '10'
else '11'
end -- case
Please can someone help, need to update using a case statement,
the field being updated is varchar (2)
ToyoGT

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Nishernd SSChasing Mays Points: 611 More actions · Answer 1

Its fine i sorted it out..

update yyyy

set TGI_CLUSTER_CODE = case

when CLUSTER_CODE in ('1M' , '2M' , '3M' , '4M' , '1T') then '01'

when CLUSTER_CODE in ('5M' , '6M' , '7M' , '2T' , '3T') then '02'

when CLUSTER_CODE in ('8M' , '9M' , '4T' ) then '03'

when CLUSTER_CODE in ('10M', '11M', '5T' ) then '04'

when CLUSTER_CODE in ('12M', '13M', '14M', '6T' ) then '05'

when CLUSTER_CODE in ('15M', '16M', '17M', '7T' ) then '06'

when CLUSTER_CODE in ('18M', '19M', '20M', '8T' ) then '07'

when CLUSTER_CODE in ('21M', '22M', '23M', '24M') then '08'

when CLUSTER_CODE in ('25M', '26M', '9T' ) then '09'

when CLUSTER_CODE in ('27M', '28M', '10T') then '10'

else '11'

end

Thanks Anyways

ToyoGT:cool:

Andras Belokosztolszki SSC-Insane Points: 22031 More actions · Answer 2

UPDATE tablename

SET TGI_CLUSTER_CODE = CASE WHEN CLUSTER_CODE IN ( '1M', '2M', '3M', '4M',

'1T' ) THEN '01'

WHEN CLUSTER_CODE IN ( '5M', '6M', '7M', '2T',

'3T' ) THEN '02'

WHEN CLUSTER_CODE IN ( '8M', '9M', '4T' )

THEN '03'

WHEN CLUSTER_CODE IN ( '10M', '11M', '5T' )

THEN '04'

WHEN CLUSTER_CODE IN ( '12M', '13M', '14M',

'6T' ) THEN '05'

WHEN CLUSTER_CODE IN ( '15M', '16M', '17M',

'7T' ) THEN '06'

WHEN CLUSTER_CODE IN ( '18M', '19M', '20M',

'8T' ) THEN '07'

WHEN CLUSTER_CODE IN ( '21M', '22M', '23M',

'24M' ) THEN '08'

WHEN CLUSTER_CODE IN ( '25M', '26M', '9T' )

THEN '09'

WHEN CLUSTER_CODE IN ( '27M', '28M', '10T' )

THEN '10'

ELSE '11'

END

Regards,

Andras

Andras Belokosztolszki, MCPD, PhD
GoldenGate Software

Andras Belokosztolszki SSC-Insane Points: 22031 More actions · Answer 3

Hmm, I did not have much time 🙂

I'm glad you have found the solution.

Regards,

Andras

Andras Belokosztolszki, MCPD, PhD
GoldenGate Software

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 4

If you are likely to use your cluster code translations again, you could put them into a table and use UPDATE...FROM

CREATE TABLE #TranslateClusterCodes (CLUSTER_CODE char(3), TGI_CLUSTER_CODE char(2))

INSERT INTO #TranslateClusterCodes (CLUSTER_CODE, TGI_CLUSTER_CODE)

SELECT '1M', '01' UNION ALL

SELECT '2M', '01' UNION ALL

SELECT '3M', '01' UNION ALL

SELECT '4M', '01' UNION ALL

SELECT '1T', '01' UNION ALL

SELECT '5M', '02' UNION ALL

SELECT '6M', '02' UNION ALL

SELECT '7M', '02' UNION ALL

SELECT '2T', '02' UNION ALL

SELECT '3T', '02' UNION ALL

SELECT '8M', '03' UNION ALL

SELECT '9M', '03' UNION ALL

SELECT '4T', '03' UNION ALL

SELECT '10M', '04' UNION ALL

SELECT '11M', '04' UNION ALL

SELECT '5T', '04' UNION ALL

SELECT '12M', '05' UNION ALL

SELECT '13M', '05' UNION ALL

SELECT '14M', '05' UNION ALL

SELECT '6T', '05' UNION ALL

SELECT '15M', '06' UNION ALL

SELECT '16M', '06' UNION ALL

SELECT '17M', '06' UNION ALL

SELECT '7T', '06' UNION ALL

SELECT '18M', '07' UNION ALL

SELECT '19M', '07' UNION ALL

SELECT '20M', '07' UNION ALL

SELECT '8T', '07' UNION ALL

SELECT '21M', '08' UNION ALL

SELECT '22M', '08' UNION ALL

SELECT '23M', '08' UNION ALL

SELECT '24M', '08' UNION ALL

SELECT '25M', '09' UNION ALL

SELECT '26M', '09' UNION ALL

SELECT '9T', '09' UNION ALL

SELECT '27M', '10' UNION ALL

SELECT '28M', '10' UNION ALL

SELECT '10T', '10'

UPDATE y SET TGI_CLUSTER_CODE = t.TGI_CLUSTER_CODE

FROM yyyy y

INNER JOIN #TranslateClusterCodes t

ON t.CLUSTER_CODE = y.CLUSTER_CODE

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden