update query - 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 2000 Forums

Transact-SQL (2000)

update query

Author

Topic

bilencekic
Posting Yak Master

121 Posts

Posted - 2006-09-01 : 10:26:06

tbl_Money
sID-----Money
1-----1000
2-----500

tbl_debts
sID----debts
1-----100
2-----240

i want a result like
tbl_Money
sID-----Money
1-----900
2-----260

i tried some queries but didint work

MS BLESS US

sanjnep
Posting Yak Master

191 Posts

Posted - 2006-09-01 : 10:35:49

update tbl_Money set Money = a.Money-b.Money from tbl_Money as a
inner join tbl_debts as b on a.sID = b.sID

bilencekic
Posting Yak Master

121 Posts

Posted - 2006-09-01 : 10:39:35

update query

MS BLESS US

bilencekic
Posting Yak Master

121 Posts

Posted - 2006-09-02 : 05:01:57

so ??
i need an update query
not a select

MS BLESS US

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-09-02 : 07:29:37

quote:

so ??
i need an update query
not a select

I dont see any where select query.. ..

try the query which is posted by sanjnep, it should be fine..

Chirag

tishri
Yak Posting Veteran

95 Posts

Posted - 2006-09-02 : 08:29:53

update m
set m.[money] = m.[money] - d.[money]
from tbl_money m
inner join tbl_debts d on m.sid=d.sid

do this...

TCC

bilencekic
Posting Yak Master

121 Posts

Posted - 2006-09-02 : 09:51:30

lol sorry yep right i though that you can use inner join in update

MS BLESS US

shaggy
Posting Yak Master

248 Posts

Posted - 2006-09-04 : 02:38:57

update tbl_Money set Money = a.Money
,a.1=b.1
,a.2=b.2
from tbl_Money a, tbl_debts b
where a.timestamp = '' (that u can see in select query)
and b.timestamp = '' (that u can see in select query)

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-09-04 : 02:45:26

quote:
Originally posted by shaggy

update tbl_Money set Money = a.Money
,a.1=b.1
,a.2=b.2
from tbl_Money a, tbl_debts b
where a.timestamp = '' (that u can see in select query)
and b.timestamp = '' (that u can see in select query)

I dont think this, what bilencekic requires..

Chirag

bilencekic
Posting Yak Master

121 Posts

Posted - 2006-09-14 : 06:15:45

damn it doenst work.
how to run this update ?
it updates all columns with the same value.

update d set Kota=Kota-(select sum(t2.DailyValue) from SKB t2 inner join tbl_Kotalar d
on d.UserID= t2.UserID
)
from tbl_Kotalar d inner join SKB t2 on
t2.USerID = d.UserID

the sum function makes it difficult

MS BLESS US

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-09-14 : 06:20:17

[code]
update d
set Kota = d.Kota - t2.DailyValue
from tbl_Kotalar d inner join SKB t2
on t2.UserID = d.UserID
[/code]

Do you have multiple record in SKB table per UserID ?

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-09-14 : 06:23:58

update d set Kota=Kota-(select sum(t2.DailyValue) from SKB t2 inner join tbl_Kotalar d
on d.UserID= t2.UserID
Group by UserID)
from tbl_Kotalar d inner join SKB t2 on
t2.USerID = d.UserID

Chirag

bilencekic
Posting Yak Master

121 Posts

Posted - 2006-09-14 : 07:02:11

yes i have multiple records per USerID.

UserID--WordID--DailyValue
1--1--100
1--2--150
1--3--200
2--1--90
.
.

so user 1 has total dailyvalue 450.
and update will get 450 then update the user1's section in tbl_Kotalar. tbl_kotalar doenst have nultiple recrods per user.

tbl_Kotalar
UserID--Kota
1-600
2-500

after update it will be like this.

1-150
2-410

MS BLESS US

bilencekic
Posting Yak Master

121 Posts

Posted - 2006-09-14 : 07:04:36

chirag:
Msg 512, Level 16, State 1, Line 1
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
The statement has been terminated.

group by returns more then 1 result
yep that is right. but it doenst update :(

MS BLESS US

bilencekic
Posting Yak Master

121 Posts

Posted - 2006-09-14 : 08:01:43

ok i have fixed it with a temp table

MS BLESS US

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-09-14 : 10:23:02

[code]
update d
set Kota = d.Kota - t2.Total_DailyValue
from tbl_Kotalar d inner join
(
select UserID, sum(DailyValue) as Total_DailyValue
from SKB
group by UserID
) t2
on t2.UserID = d.UserID
[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources