Stored Procedure without a CASE Statement

My problem is i want to know how to write an sql statement that will increase all acounts with balances over $10,0000 by 6%, all other accounts receive 5% (without using a CASE statement) The table involve in the statement is account table with the followin开发者_如何转开发g fields: (account_number(PK), branch_name, balance). Thanks in advance...

Just for fun, this version of ypercube's script has only 1 update, and should do the job:

UPDATE tableX
SET balance = balance * (1 + ((5.00 + convert(bit,floor(balance/10000))) / 100))

Note: I've used 5.00 to force the division into decimals, and not round off as an int. You could also do this by doing it properly and converting to a decimal.

I wonder why CASE cannot be used. Anyway:

UPDATE tableX
SET  balance = balance * (1 + 0.06)
WHERE balance > 10000 ;

UPDATE tableX
SET  balance = balance * (1 + 0.05)
WHERE balance <= 10000
  AND balance > 0 ;

You probably want to put this inside a transaction.

Without using CASE and with only one UPDATE:

UPDATE x
SET x.balance = x.balance * (1 + y.raise/100)
FROM tableX AS x
  JOIN
    ( SELECT 0 AS low, 10000 AS high, 5.0 AS raise
    UNION
      SELECT 10000, 9999999999, 6.0 
    ) AS y
    ON  x.balance > y.low 
    AND x.balance <= y.high

And one more approach, just for fun:

UPDATE tableX AS x
SET balance = balance * 
  ( SELECT TOP 1
        change
    FROM 
      ( SELECT 0 AS low, 1.05 AS change
      UNION
        SELECT 10000,    1.06 
      ) AS y
    WHERE balance > low
    ORDER BY low DESC
  )
WHERE balance > 0 

or this query :)), but is more stpid than Jon's query.

update tableX a set ballance = 
                    (select  ballance*(1+6/100) as  ballance_new 
                    from tableX b
                    where ballance >=10000 and b.account_name = a.account_name 
                    union all
                    select ballance*(1+5/100) as  ballance_new c
                    from tableX
                    where ballance <10000 and c.account_name = a.account_name) 

I think there is room for 1 more simple solution. Here it is:

UPDATE @t
SET balance = (SELECT balance* 1.05 where balance <= 10000
         UNION SELECT balance* 1.06 where balance > 10000)

Revised @AndriyM's surgested to use UNION ALL instead of UNION for better performance

UPDATE @t
SET balance = (SELECT balance* 1.05 where balance <= 10000
     UNION ALL SELECT balance* 1.06 where balance > 10000)