Select Update - SQL Server Forums

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)

Select Update

Author

Topic

cable_si
Starting Member

20 Posts

Posted - 2011-05-06 : 07:56:11

Hi

I am trying to create a select update statement which sets the cost value based upon the size of a message

Sales.messagecost is a money field (i.e 6p or 0.06)

if the length of the message is less than 160 characters the cost should be 6p, if the length is greater that 160 then the message is charged double, ie 12p

below is what i have got so far

UPDATE invoice_table
SET cost =
(select SUM(CASE WHEN (LEN(invoice_table.msg) <= 160) THEN messagecost ELSE 0 END,
SUM(CASE WHEN (LEN(invoice_table.msg) > 160) THEN messagecost*2 ELSE 0 END)
from sales where sales.companyid = invoice_table.companyid)
where companyid=@c_id

can anyone help at all

thanks

Simon

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-05-06 : 08:08:07

Would this work?

UPDATE i SET
   cost = SUM
   ( 
      CASE WHEN LEN(i.msg) <= 160 THEN messagecost
      ELSE 2.0*messagecost END
   )
FROM
   sales s
   INNER JOIN invoice_table i 
      ON i.companyid = s.companyid
WHERE
      i.companyid = @c_id

lionofdezert
Aged Yak Warrior

885 Posts

Posted - 2011-05-06 : 08:12:41

sunita you are so quick.

--------------------------
http://connectsql.blogspot.com/

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-05-06 : 08:15:17

quote:
Originally posted by lionofdezert

sunita you are so quick.

--------------------------
http://connectsql.blogspot.com/

Heh! Shhh..... Don't tell anyone, but I did not test it, so it may not work at all!!

cable_si
Starting Member

20 Posts

Posted - 2011-05-06 : 08:50:09

quote:

Originally posted by sunitabeck

Would this work?

UPDATE i SET
   cost = SUM
   ( 
      CASE WHEN LEN(i.msg) <= 160 THEN messagecost
      ELSE 2.0*messagecost END
   )
FROM
   sales s
   INNER JOIN invoice_table i 
      ON i.companyid = s.companyid
WHERE
      i.companyid = @c_id

thanks for that, but when i test it i get the error

An aggregate may not appear in the set list of an UPDATE statement.

any ideas ?

thanks again

lionofdezert
Aged Yak Warrior

885 Posts

Posted - 2011-05-06 : 09:13:41

Why you need SUM here?

--------------------------
http://connectsql.blogspot.com/

cable_si
Starting Member

20 Posts

Posted - 2011-05-06 : 09:28:17

thanks for your help peeps i have managed to sort it now

shamasm
Starting Member

11 Posts

Posted - 2011-05-09 : 08:02:59

i think this may work

update I
set cost = qww.costMess
from
(
select sum(costmess) costMess from
(
select CASE WHEN (LEN(invoice_table.msg) <= 160)
THEN messagecost ELSE 2*messagecost END as costMess
from sales inner join I on sales.companyid = invoice_table.companyid
)s )
where companyid=@c_id

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources