add two values and update table

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)

add two values and update table

Author

Topic

lovehui
Yak Posting Veteran

60 Posts

Posted - 2009-03-27 : 15:49:06

Hello,

I am going update a table by adding two values x and y.

Can you help me?

Appreciate your watch and time.

Update #table1
set total = select x from t1 where c1=3
      +
     select y from t2 where d1=5
from @table1

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-03-27 : 16:35:46

This will update total column in #table1 to the sum of x,y from t1,t2. It will result in an error incase subquery reurns more than one value.


Update #table1
set total = (select x from t1 where c1=3)
      		+
     	    (select y from t2 where d1=5)

I am not sure what you want though. Let us know if you are facing problems.

lovehui
Yak Posting Veteran

60 Posts

Posted - 2009-03-27 : 16:58:02

Well, I have a long query. The result should not be 0 but I got 0.

select @qry=
'update #TmpJnlData
set SYSTEM_TOTAL=(select sum(L.AMT) from  LOAN_PICKUP L inner join EMPLOYEE E
					 on (E.EMP_ID = L.EMP_ID)

inner join TRANS_HEADER TR
on L.TRAN_ID=TR.TRAN_ID
inner join TENDER T
ON T.TND_CD=L.TND_CD
inner join REASONCODES R
on Replace(T.TND_DESCR,''CDN '','''')=replace(R.CODEDESC,''Reason'','''')
where' + @filterByPeriod +' and L.TND_CD =1 and TR.TRAN_CD=25)

              +

(select sum(L.AMT) from  LOAN_PICKUP L inner join EMPLOYEE E
					 on (E.EMP_ID = L.EMP_ID)

inner join TRANS_HEADER TR
on L.TRAN_ID=TR.TRAN_ID
inner join TENDER T
ON T.TND_CD=L.TND_CD
inner join REASONCODES R
on Replace(T.TND_DESCR,''CDN '','''')=replace(R.CODEDESC,''Reason'','''')
where' + @filterByPeriod +' and L.TND_CD =1 and TR.TRAN_CD=27)
from  #TmpJnlData'
exec(@qry)

I don't know why?

Thanks again.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-03-28 : 04:04:06

whats the need of dynamic sql here? what will @filterByPeriod have as values?

lovehui
Yak Posting Veteran

60 Posts

Posted - 2009-03-28 : 17:03:51

Just ignore @filterByPeriod
String @filterByPeriod look like

Between 'Mar 29 2009 00:00:00 AM' and 'Mar 29 2009 23:59:59 PM'
L.AMT is a money type.
Shall I convert it to some type?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-03-29 : 03:34:26

quote:
Originally posted by lovehui

Just ignore @filterByPeriod
String @filterByPeriod look like

Between 'Mar 29 2009 00:00:00 AM' and 'Mar 29 2009 23:59:59 PM'
L.AMT is a money type.
Shall I convert it to some type?

why do you pass filetr like this? isnt it better to keep two parameters @Start & @End and then use BETWEEN @Start AND @End rather than passing entire BETWEEN... statement through parameter?

lovehui
Yak Posting Veteran

60 Posts

Posted - 2009-03-29 : 11:14:11

quote:
Originally posted by visakh16

quote:
Originally posted by lovehui

Just ignore @filterByPeriod
String @filterByPeriod look like

Between 'Mar 29 2009 00:00:00 AM' and 'Mar 29 2009 23:59:59 PM'
L.AMT is a money type.
Shall I convert it to some type?

why do you pass filetr like this? isnt it better to keep two parameters @Start & @End and then use BETWEEN @Start AND @End rather than passing entire BETWEEN... statement through parameter?

Well, I can do it like you said.
But do you think is it the real reason causing the wrong result?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources