help on making relation between two tables

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)

help on making relation between two tables

Author

Topic

bilencekic
Posting Yak Master

121 Posts

Posted - 2006-01-17 : 19:34:02

first Table
ID = primary key and identity(1,1)

ID Food
1 100
2 300
3 400

second table.

ID RID Food
1 1 50
2 1 70
3 2 30
4 3 50
.
.
.

goes like this.
how can i update table 1 by getting values from the table 2?
firsttable will be like that

ID Food
1 220
.
.
.

MS BLESS US

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-01-17 : 20:04:54

If i understand you correctly, something like this ?

update 	t1
	set	t1.Food = t1.Food + (select sum(t2.Food) from table2 t2 where t2.RID = t1.ID)
	from	table1 t1

-----------------
'KH'

bilencekic
Posting Yak Master

121 Posts

Posted - 2006-01-17 : 20:36:21

update [A] set YiyecekGeliri = YiyecekGeliri +
(Select count([A].OyuncuID)* 5 from [B] inner join [A] on [A].OyuncuID =
[B].OyuncuID
where BinaID = 11 and [B].OyuncuID = [A].OyuncuID)

that doesnt work it effects all rows.
Table B contains
ID OyuncuID BinaID
1 9 11
2 9 11
3 5 2
4 9 18

on my table A
i only want to update the column YiyecekGeliri by getting the number of BinaID which has BinaID = 11

MS BLESS US

bilencekic
Posting Yak Master

121 Posts

Posted - 2006-01-17 : 20:37:29

if i
update [A] set YiyecekGeliri = YiyecekGeliri +
(Select count(OyuncuID) from [B] where BinaID = 11 and [B].OyuncuID = [A].OyuncuID)

use this
all rows in table A effected
i dont want this

MS BLESS US

bilencekic
Posting Yak Master

121 Posts

Posted - 2006-01-17 : 20:49:30

i tried this but when subquery returns more then 1 value it gives error.
--

declare @Oyun int
set @Oyun = (select distinct [B].OyuncuID from [B] where BinaID = 11)
update [A] set YiyecekGeliri = YiyecekGeliri +
(Select count([A].OyuncuID)* 5 from [B] inner join [B] on [A].OyuncuID =
[B].OyuncuID
where BinaID = 11 and [A].OyuncuID = @Oyun) where [B].OyuncuID = @Oyun

MS BLESS US

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-01-17 : 21:10:45

use exists to limit the record for updates

update 	t1
	set	t1.Food = t1.Food + (select sum(t2.Food) from table2 t2 where t2.RID = t1.ID)
	from	table1 t1
	where	exists (select * from table2 x where x.RID = t1.ID)

-----------------
'KH'

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources