Repalcing the CASE statement in the update

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Repalcing the CASE statement in the update

Author

Topic

8022421
Starting Member

45 Posts

Posted - 2008-12-18 : 09:38:03

This table has millions of record. so when i am using a case statement in the update statement it takes time can this be written in a more optimized way.

UPDATE table
SET AUTO_REIN_OPT_OUT = CASE WHEN PRODUCT_CODE = '00' AND COVERAGE_CODE = '01' AND SS_CODE_VALUE = '00'
THEN AUTO_REIN_OPT_OUT
ELSE ''
END
FROM table

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-12-18 : 09:52:26

[code]UPDATE Table
SET AUTO_REIN_OPT_OUT = ''
WHERE NOT (PRODUCT_CODE = '00' AND COVERAGE_CODE = '01')[/code]

E 12°55'05.63"
N 56°04'39.26"

8022421
Starting Member

45 Posts

Posted - 2008-12-18 : 09:58:30

Thanks peso.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-12-18 : 10:02:00

Do the query go faster now?

E 12°55'05.63"
N 56°04'39.26"

8022421
Starting Member

45 Posts

Posted - 2008-12-18 : 11:14:14

Yes it do..
When the below update statement split into different update will increase the performance?, As i am eliminating the case statement.
UPDATE table
SET TOT_PREMIUM_PAID_STAGE = CASE WHEN (BENEFIT_TYPE = 'BA' OR BENEFIT_TYPE = 'OR')
THEN TAX_BASIS
ELSE CASE WHEN BENEFIT_TYPE = 'SU'
THEN SU_TAX_BASIS
END
END
FROM table

or is it possible to write a single update itself without a case statement.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-12-18 : 11:21:00

Your UPDATE statement is functional to this

UPDATE	Table1
SET	TOT_PREMIUM_PAID_STAGE =	CASE BENEFIT_TYPE 
						WHEN 'BA' THEN TAX_BASIS
						WHEN 'OR' THEN TAX_BASIS
						WHEN 'SU' THEN SU_TAX_BASIS
						ELSE NULL
					END

Beware what happens when BENEFIT_TYPE is not one of the three types.
Maybe you want something like this?

UPDATE	Table1
SET	TOT_PREMIUM_PAID_STAGE =	CASE BENEFIT_TYPE 
						WHEN 'BA' THEN TAX_BASIS
						WHEN 'OR' THEN TAX_BASIS
						WHEN 'SU' THEN SU_TAX_BASIS
					END
WHERE	BENEFIT_TYPE IN ('BA', 'OR', 'SU')

E 12°55'05.63"
N 56°04'39.26"

8022421
Starting Member

45 Posts

Posted - 2008-12-18 : 11:56:49

Thanks peso, it works..One more help..Can this be rewritten in a better way,
UPDATE table
SET PREM_PAY_PERIOD_ATISS = CASE WHEN ( PRODUCT_CODE = '00' AND (COVERAGE_CODE >= '60' OR COVERAGE_CODE = '06' OR COVERAGE_CODE = '45'))
THEN CASE WHEN COVERAGE_CODE <> '80'
THEN ''
ELSE PREM_PAY_PERIOD_ATISS
END
ELSE PREM_PAY_PERIOD_ATISS
END,
BEN_PERIOD_ATISS = CASE WHEN ( PRODUCT_CODE = '00' AND (COVERAGE_CODE >= '60' OR COVERAGE_CODE = '06' OR COVERAGE_CODE = '45'))
THEN CASE WHEN COVERAGE_CODE <> '80'
THEN ''
ELSE BEN_PERIOD_ATISS
END
ELSE BEN_PERIOD_ATISS
END,
FROM
table

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources