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
 General SQL Server Forums
 New to SQL Server Programming
 sql query help

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.id
FROM @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]

Go to Top of Page

nextaxtion
Yak Posting Veteran

54 Posts

Posted - 2013-12-03 : 01:41:43
TABLE @A.ID1 = @B.ID AND @C.ID1 = @B.ID

prithvi nath pandey
Go to Top of Page

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]

Go to Top of Page

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
Go to Top of Page

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 C
update	a
set ID2 = c.ID
from (
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]

Go to Top of Page

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 be
select	a_ID 	= a.ID,
a_ID1 = a.ID1,
a_ID2 = a.ID2,
b_ID = b.ID,
c_ID = c.ID,
c_ID1 = c.ID1
from @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 1
1 1 NULL 1 2 1
2 1 NULL 1 1 1
2 1 NULL 1 2 1

(4 row(s) affected)




KH
[spoiler]Time is always against us[/spoiler]

Go to Top of Page
   

- Advertisement -