sql server - Change Primary Key Master Details Tables SQL -

sql server - Change Primary Key Master Details Tables SQL -

i have master details tables

mastertbl

id name age 12 name1 15 544 name2 15 2544 name3 15

detailstbl

id session masterid 1 test 12 2 test2 12 3 test3 544 4 test4 2544 5 test5 12 6 test6 544

is possible alter id of master table , details table without info conflict :

mastertbl

id name age 1 name1 15 2 name2 15 3 name3 15

detailstbl

id session masterid 1 test 1 2 test2 1 3 test3 2 4 test4 3 5 test5 1 6 test6 2

i used function in master

row_number() on (order id)

but should update details

try this:

declare @temp table (tempid int identity(1,1), id int) insert @temp (id) select id mastertbl set @total = @@rowcount declare @count int = 1 declare @id int while @count <= @total begin set @id = (select id @temp tempid = @count) -- update details table update detailstbl set masterid = @count masterid = @id -- update master table update mastertbl set id = @count id = @id set @count = @count + 1 end

first need update details table, master table.

sql sql-server sql-server-2012