October 6, 2008 at 11:02 pm
i hv a master table say student as
id , name
10, a
11, b
12, c
13, d
14, e
15, f
16, g
17, h
18, i
and some transaction tables as
trans1
id,tel,tel2 ,etc
trnas2
id, address2, add3, etc
now it was found later that some id are wrong ordered so it must be like this
id 10 must have been 11
11 10
15 11
14 10
12 15
16 12
suggest some script
October 6, 2008 at 11:15 pm
Hi
From what i have understood from your post and this is the scenario...
lets say id 10 must be 11 and id 11 must be 12
first update id 11 to 12 and then update id 10 to 11.
U need to work out the "chain" and then start from the last link i.e. in our example change 11 to 12 and then 10 to 11.
ur update statement would be
update table1 set id = 12 where id = 11
update table1 set id = 11where id = 10.
"Keep Trying"
October 7, 2008 at 1:03 am
thanks
well m doing same thing i thought there cud be some other better n short method. my emphasis was to change transaction tables too. coz thre are no of transaction tables depending on the master table , i have to update them too, which wud be very time consuming,
Anyways thanks . i ll try
October 7, 2008 at 1:15 am
Another approach:
Keep another table preferrable a temp table that contains the existing id's and the new value for these ids.
Lookup on this table and then run ur update on the required tables in the same manner pointed out before i.e starting from the last link in the chain.
Another approach would be to have new column in the existing tables. This new column would store the existing id.
U can join on this new column and update ur id column.
You will have to drop this column after ur updation.
"Keep Trying"
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply