CODEWITHSUNDEEP
sqloracle-database
Larry Martell
Larry Martell

Reputation: 3756

conditionally update column with SQL (in Oracle)

Is there a way in SQL to conditionally update a column with one or another value?

I have this update sql (paraphrased):

UPDATE LMPROC_LIMITS
   SET LIMIT = sign(LIMIT) * 100 * floor(0.000001 + (sign(LIMIT) * LIMIT * ratio/100)
 WHERE SYMBOL_ID = symbolId
   AND CLASSTYPE = LimitType
   AND TYPE_ IN (
                 'minClusterPosition', 
                 'maxClusterPosition',
                 'minProductPosition', 
                 'maxProductPosition', 
                 'minBookPosition',
                 'maxBookPosition', 
                 'maxShortShares'
                )

(ratio, symbolId, and LimitType all are filled in at run time)

What I want to do is limit (no pun intended) the value LIMIT is set to to +/-2,147,483,647 - i.e. if the result of (sign(LIMIT) * 100 * floor(0.000001 + (sign(LIMIT) * LIMIT * ratio/100)) is more or less then that, I want to set it to +/-2,147,483,647 Can I do this in SQL?

This is in Oracle

Upvotes: 0

Views: 199

Answers (1)

het2cz
het2cz

Reputation: 166

You can use CASE statement:

update LMPROC_LIMITS
set LIMIT = 
  case 
    when sign(LIMIT) * 100 * floor(0.000001 + (sign(LIMIT) * LIMIT * ratio/100) > 2147483647 then 2147483647
    when sign(LIMIT) * 100 * floor(0.000001 + (sign(LIMIT) * LIMIT * ratio/100) < -2147483647 then - 2147483647
    else sign(LIMIT) * 100 * floor(0.000001 + (sign(LIMIT) * LIMIT * ratio/100)
  end       
where SYMBOL_ID = symbolId
and CLASSTYPE = LimitType
and TYPE_ in ('minClusterPosition', 'maxClusterPosition',
'minProductPosition', 'maxProductPosition', 'minBookPosition',
'maxBookPosition', 'maxShortShares')

Also in your specific example combination of GREATEST and LEAST function would do the trick as well.

Upvotes: 3

Related Questions