SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 sql query help
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

nextaxtion
Starting Member

India
30 Posts

Posted - 12/03/2013 :  00:46:14  Show Profile  Reply with Quote
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)

Singapore
17587 Posts

Posted - 12/03/2013 :  01:39:26  Show Profile  Reply with Quote
This is confusing.

Can you explain the relationship between the tables ? How is 2 table related ? via which columns ?




KH
Time is always against us

Go to Top of Page

nextaxtion
Starting Member

India
30 Posts

Posted - 12/03/2013 :  01:41:43  Show Profile  Reply with Quote
TABLE @A.ID1 = @B.ID AND @C.ID1 = @B.ID

prithvi nath pandey
Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17587 Posts

Posted - 12/03/2013 :  01:56:19  Show Profile  Reply with Quote
that would result in many to many, any relationship between @A and @C ?


KH
Time is always against us

Go to Top of Page

nextaxtion
Starting Member

India
30 Posts

Posted - 12/03/2013 :  02:16:49  Show Profile  Reply with Quote
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)

Singapore
17587 Posts

Posted - 12/03/2013 :  02:27:12  Show Profile  Reply with Quote
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
Time is always against us

Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17587 Posts

Posted - 12/03/2013 :  02:31:21  Show Profile  Reply with Quote
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
Time is always against us

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000