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
 SQL Server 2000 Forums
 SQL Server Development (2000)
 How to update foreign key in tbl B based on tbl A?
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

raffiq_eddy
Starting Member

12 Posts

Posted - 08/09/2007 :  04:54:39  Show Profile  Reply with Quote
Hi SQL Expert,

I want to update the foreign key in table B according to table A, please help me

E.g.

Table A
PK1	PK2
1	1
1	2
1	3


Table B (before)
PK	FK1	FK2
A	1	1
B	1	4
C	1	5

I want the result as following...


Table B (after)
PK	FK1	FK2
A	1	1
B	1	2*
C	1	3*


* changes only apply to this data

Is this possible, can this be done using only 1 sql statement?

TIA

Edited by - raffiq_eddy on 08/09/2007 04:59:16

nr
SQLTeam MVY

United Kingdom
12543 Posts

Posted - 08/09/2007 :  05:16:34  Show Profile  Visit nr's Homepage  Reply with Quote
update TableB
set FK2 = at.PK2
from
TableB b
join
(select PK, FK1, FK2, seq = (select count(*) from TableB b2 where b2.FK1 = b.FK1 and b2.FK2 <= b.FK2) from TableB b) bt
on b.PK = bt.PK
(select PK1, PK2, seq = (select count(*) from TableA a2 where a2.PK1 = a.PK1 and a2.PK2 <= a.PK2) from TableA a) at
join bt
on at.PK1 = bt.FK1
and at.seq = bt.seq



==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.
Go to Top of Page

raffiq_eddy
Starting Member

12 Posts

Posted - 08/10/2007 :  22:23:35  Show Profile  Reply with Quote
thanks for prompt reply nr!

sorry for the late reply from me though

I'll try ur solution now



quote:
Originally posted by nr

update TableB
set FK2 = at.PK2
from
TableB b
join
(select PK, FK1, FK2, seq = (select count(*) from TableB b2 where b2.FK1 = b.FK1 and b2.FK2 <= b.FK2) from TableB b) bt
on b.PK = bt.PK
(select PK1, PK2, seq = (select count(*) from TableA a2 where a2.PK1 = a.PK1 and a2.PK2 <= a.PK2) from TableA a) at
join bt
on at.PK1 = bt.FK1
and at.seq = bt.seq


Go to Top of Page

raffiq_eddy
Starting Member

12 Posts

Posted - 08/11/2007 :  02:40:31  Show Profile  Reply with Quote
I tried to run the script given but query analyzer return this error:

Line 7: Incorrect syntax near 'at'.

which highlighted this line...

(select PK1, PK2, seq = (select count(*) from TableA a2 where a2.PK1 = a.PK1 and a2.PK2 <= a.PK2) from TableA a) at



quote:
Originally posted by nr

update TableB
set FK2 = at.PK2
from
TableB b
join
(select PK, FK1, FK2, seq = (select count(*) from TableB b2 where b2.FK1 = b.FK1 and b2.FK2 <= b.FK2) from TableB b) bt
on b.PK = bt.PK
(select PK1, PK2, seq = (select count(*) from TableA a2 where a2.PK1 = a.PK1 and a2.PK2 <= a.PK2) from TableA a) at
join bt
on at.PK1 = bt.FK1
and at.seq = bt.seq

Go to Top of Page

nr
SQLTeam MVY

United Kingdom
12543 Posts

Posted - 08/12/2007 :  14:22:40  Show Profile  Visit nr's Homepage  Reply with Quote
update TableB
set FK2 = at.PK2
from
TableB b
join
(select PK, FK1, FK2, seq = (select count(*) from TableB b2 where b2.FK1 = b.FK1 and b2.FK2 <= b.FK2) from TableB b) bt
on b.PK = bt.PK
join
(select PK1, PK2, seq = (select count(*) from TableA a2 where a2.PK1 = a.PK1 and a2.PK2 <= a.PK2) from TableA a) at
on at.PK1 = bt.FK1
and at.seq = bt.seq

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.
Go to Top of Page

raffiq_eddy
Starting Member

12 Posts

Posted - 08/13/2007 :  01:25:13  Show Profile  Reply with Quote
It works!!

Thank you very much nr!

Regards

quote:
Originally posted by nr

update TableB
set FK2 = at.PK2
from
TableB b
join
(select PK, FK1, FK2, seq = (select count(*) from TableB b2 where b2.FK1 = b.FK1 and b2.FK2 <= b.FK2) from TableB b) bt
on b.PK = bt.PK
join
(select PK1, PK2, seq = (select count(*) from TableA a2 where a2.PK1 = a.PK1 and a2.PK2 <= a.PK2) from TableA a) at
on at.PK1 = bt.FK1
and at.seq = bt.seq

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Go to Top of Page

Hommer
Aged Yak Warrior

784 Posts

Posted - 08/13/2007 :  11:19:55  Show Profile  Reply with Quote
I found out this will also do the trick. I am not sure I follow the
logic of the 2nd join, but the way nr used the seq part is cool.


update TableB
set FK2 = bt.seq
from
TableB b
join
(select PK, FK1, FK2, seq = (select count(*) from TableB b2 where b2.FK1 = b.FK1 and b2.FK2 <= b.FK2) from TableB b) bt
on b.PK = bt.PK
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.08 seconds. Powered By: Snitz Forums 2000