将子行移动到新的父 FK ID? [英] Move Child Rows to new Parent FK ID?

问题描述

SQL Server 2008.

SQL Server 2008.

我有一个 pk id 为 1 的父行.在阻止所有其他数据库用户的同时(这是一个清理操作，因此资源争用不是问题)，我想插入一个新行，然后获取所有子行并将他们的 fk 列更改为新行.以下面的 DDL 为例，我想插入一个新行，并为所有 #chi.parid 值赋予一个值3"，这样它们现在基本上属于新行，因此可以删除旧行.

I have a parent row with pk id 1. While blocking all other DB users (this is a cleanup operation so resource contention is not an issue), I would like to insert a new row, then take all of the child rows and change their fk column to the new row. With the below DDL for example, I would like to insert a new row and give all of the #chi.parid values a value of '3' so they would essentially now belong to the new row so the old one can be deleted.

帮助！

create table #par ( parid int identity(1,1) , note varchar(8) )
create table #chi ( chiid int identity(1,1) , parid int , thing varchar(8) )
insert into #par values ( 'note1' )
insert into #par values ( 'note2' )
insert into #chi values ( 1 , 'a' )
insert into #chi values ( 1 , 'b' )
insert into #chi values ( 1 , 'c' )

推荐答案

感谢大家的投入.看来我不能"用 SQL Svr 2008 对此进行基于集合的操作，所以我用循环做了 RBAR 解决方案(我认为它比游标性能更好).任何可以评论使用 try..catch 使其更安全的人，或者在集合中对我进行更多操作时，请发表评论.:)

Thanks all for the input. It appears I "can't" do a set-based operation on this with SQL Svr 2008, so I did RBAR solution with a loop (I think it performs better than a cursor). Anyone who can comment on making this safer with try..catch or enlighten me more on doing this in a set, please comment. :)

谢谢.

    Select 
                [parid]
            ,   [name]
    Into    #redo
    From    partable 
    Where   DateDiff( Hour , donewhen ,SysDateTimeOffset() ) > 23
Begin Transaction
Declare @rows int  = ( Select COUNT(*) From #redo )
Declare @parid int 
Create Clustered Index redoix on #redo([parid]) With FillFactor = 100
While @rows > 0
Begin
    Select Top 1 @parid = [parid] from #redo Order By parid Asc

        Insert partable 
            (
                [name]
            )
        Select 
                [name]      
        From #redo 
        Where parid = @parid
        Update chitable
            Set parid = Scope_Identity()
            Where   parid = @parid
        Delete From partable
            Where   parid = @parid

    Delete from #redo where [parid] = @parid 
    Set @rows  = ( Select COUNT(*) From #redo )
End
Commit Transaction

这篇关于将子行移动到新的父 FK ID?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！