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 |
raffiq_eddy
Starting Member
12 Posts |
Posted - 2007-08-09 : 04:54:39
|
Hi SQL Expert,I want to update the foreign key in table B according to table A, please help meE.g.Table APK1 PK21 11 21 3 Table B (before)PK FK1 FK2A 1 1B 1 4C 1 5 I want the result as following...Table B (after)PK FK1 FK2A 1 1B 1 2*C 1 3* * changes only apply to this dataIs this possible, can this be done using only 1 sql statement?TIA |
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2007-08-09 : 05:16:34
|
update TableBset FK2 = at.PK2fromTableB bjoin(select PK, FK1, FK2, seq = (select count(*) from TableB b2 where b2.FK1 = b.FK1 and b2.FK2 <= b.FK2) from TableB b) bton 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) atjoin bton at.PK1 = bt.FK1and 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. |
|
|
raffiq_eddy
Starting Member
12 Posts |
Posted - 2007-08-10 : 22:23:35
|
thanks for prompt reply nr!sorry for the late reply from me thoughI'll try ur solution nowquote: Originally posted by nr update TableBset FK2 = at.PK2fromTableB bjoin(select PK, FK1, FK2, seq = (select count(*) from TableB b2 where b2.FK1 = b.FK1 and b2.FK2 <= b.FK2) from TableB b) bton 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) atjoin bton at.PK1 = bt.FK1and at.seq = bt.seq
|
|
|
raffiq_eddy
Starting Member
12 Posts |
Posted - 2007-08-11 : 02:40:31
|
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) atquote: Originally posted by nr update TableBset FK2 = at.PK2fromTableB bjoin(select PK, FK1, FK2, seq = (select count(*) from TableB b2 where b2.FK1 = b.FK1 and b2.FK2 <= b.FK2) from TableB b) bton 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) atjoin bton at.PK1 = bt.FK1and at.seq = bt.seq
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2007-08-12 : 14:22:40
|
update TableBset FK2 = at.PK2fromTableB bjoin(select PK, FK1, FK2, seq = (select count(*) from TableB b2 where b2.FK1 = b.FK1 and b2.FK2 <= b.FK2) from TableB b) bton b.PK = bt.PKjoin(select PK1, PK2, seq = (select count(*) from TableA a2 where a2.PK1 = a.PK1 and a2.PK2 <= a.PK2) from TableA a) aton at.PK1 = bt.FK1and 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. |
|
|
raffiq_eddy
Starting Member
12 Posts |
Posted - 2007-08-13 : 01:25:13
|
It works!!Thank you very much nr!Regardsquote: Originally posted by nr update TableBset FK2 = at.PK2fromTableB bjoin(select PK, FK1, FK2, seq = (select count(*) from TableB b2 where b2.FK1 = b.FK1 and b2.FK2 <= b.FK2) from TableB b) bton b.PK = bt.PKjoin(select PK1, PK2, seq = (select count(*) from TableA a2 where a2.PK1 = a.PK1 and a2.PK2 <= a.PK2) from TableA a) aton at.PK1 = bt.FK1and 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.
|
|
|
Hommer
Aged Yak Warrior
808 Posts |
Posted - 2007-08-13 : 11:19:55
|
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 TableBset FK2 = bt.seqfromTableB bjoin(select PK, FK1, FK2, seq = (select count(*) from TableB b2 where b2.FK1 = b.FK1 and b2.FK2 <= b.FK2) from TableB b) bton b.PK = bt.PK |
|
|
|
|
|
|
|