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.
Author |
Topic |
nextaxtion
Yak Posting Veteran
54 Posts |
Posted - 2013-12-03 : 00:46:14
|
hi team, i have 3 table @, @b ,@c .i want to update col id2 of table @a with matching values of @c.id where relation ship exist between @a and @c with table @b.id. DECLARE @A TABLE (ID NUMERIC(10) , ID1 NUMERIC(10) , ID2 NUMERIC(10)) DECLARE @B TABLE (ID NUMERIC(10) ) DECLARE @C TABLE (ID NUMERIC(10) ,ID1 NUMERIC ) INSERT INTO @B (ID)VALUES(1)INSERT INTO @A (ID,ID1 ,ID2)VALUES(1 , 1 ,NULL)INSERT INTO @A (ID,ID1 ,ID2)VALUES(2 , 1 ,NULL)INSERT INTO @C (ID,ID1 )VALUES(1 , 1 )INSERT INTO @C (ID,ID1 )VALUES(2 , 1 ) table : @a ID ID1 ID2 1 1 NULL 2 1 NULL table : @b ID 1 table : @c ID ID1 1 1 2 1 i want result as select * from @a ID ID1 ID2 1 1 1 2 1 2 my query does not update correctly UPDATE T SET ID2 = t2.idFROM @A T , @B T1 ,@c t2 WHERE T.ID1 = T1.ID prithvi nath pandey |
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2013-12-03 : 01:39:26
|
This is confusing. Can you explain the relationship between the tables ? How is 2 table related ? via which columns ? KH[spoiler]Time is always against us[/spoiler] |
|
|
nextaxtion
Yak Posting Veteran
54 Posts |
Posted - 2013-12-03 : 01:41:43
|
TABLE @A.ID1 = @B.ID AND @C.ID1 = @B.IDprithvi nath pandey |
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2013-12-03 : 01:56:19
|
that would result in many to many, any relationship between @A and @C ? KH[spoiler]Time is always against us[/spoiler] |
|
|
nextaxtion
Yak Posting Veteran
54 Posts |
Posted - 2013-12-03 : 02:16:49
|
i need to update a.id2 with c.id where a.id1 = b.id and b.id = c.id1 ... prithvi nath pandey |
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2013-12-03 : 02:27:12
|
As highlighted, based on your relationship, there will be many to many matching. To give you the result that you want, but it is based on the row numbering of @A and @C matching first row of A to C and second row of A to second row of Cupdate aset ID2 = c.IDfrom ( select *, rn = row_number() over (order by ID) from @A ) a INNER JOIN @B b on a.ID1 = b.ID INNER JOIN ( select *, rn = row_number() over (order by ID) from @C ) c on b.ID = c.ID1 and a.rn = c.rn KH[spoiler]Time is always against us[/spoiler] |
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2013-12-03 : 02:31:21
|
to further illustrate what i mean earlier,quote: Originally posted by nextaxtion i need to update a.id2 with c.id where a.id1 = b.id and b.id = c.id1 ... prithvi nath pandey
based on what you specify here, the query will beselect a_ID = a.ID, a_ID1 = a.ID1, a_ID2 = a.ID2, b_ID = b.ID, c_ID = c.ID, c_ID1 = c.ID1from @A a INNER JOIN @B b on a.ID1 = b.ID INNER JOIN @C c on b.ID = c.ID1 And the result will be a_ID a_ID1 a_ID2 b_ID c_ID c_ID1 ------------ ------------ ------------ ------------ ------------ -------------------- 1 1 NULL 1 1 11 1 NULL 1 2 12 1 NULL 1 1 12 1 NULL 1 2 1(4 row(s) affected) KH[spoiler]Time is always against us[/spoiler] |
|
|
|
|
|
|
|