代码之家  ›  专栏  ›  技术社区  ›  Alireza Noori

更新表并插入丢失的记录

  •  0
  • Alireza Noori  · 技术社区  · 7 年前

    我有一个带有外键列的表,其中有一些空记录。我可以选择缺少列的记录,例如:

    SELECT * FROM Outgoing WHERE Receipt_Id IS NULL
    

    Receipts Id 并将其设置为的值 Receipt_Id

    这在查询中可能吗?

    2 回复  |  直到 7 年前
        1
  •  0
  •   Yogesh Sharma    7 年前

    看来你在找我 inserted

    INSERT INTO Receipts (col1, col2....)
    OUTPUT INSERTED.*
    INTO @CreatedIds -- TEMP TABLE WHICH HOLDS RECENTLY INERTED DATA INCLUDING Receipt_Id (pk)
    SELECT col1, col2.... 
    FROM Outgoing 
    WHERE Receipt_Id IS NULL
    

    SELECT c.*
    FROM @CreatedIds c -- Note this is a table variable that you need to manual create.
    
        2
  •  0
  •   DhruvJoshi    7 年前

    更新:

    最新答复:

    您所需要做的就是创建一个序列,而不是一个只有一列的表。然后用序列号更新传出表。

       --create table Outgoing ( id int Primary Key IDENTITY(1,1),data nvarchar(100), record_id int);
    --insert into Outgoing values ('john',NULL),('jane',NULL),('jean',NULL);
    
    create sequence dbo.receipts as int start with 1 increment by 1;
    
    update Outgoing
    set record_id= NEXT VALUE FOR dbo.receipts
    where record_id is null
    
    select * from Outgoing
    

    See working demo

    下面是老答案

    ID 这两个表中的列都可以更新 Receipt_Id 基于此列返回到 Outgoing

    因此,您的步骤是: 1.插入记录

    DECLARE @LastRID bigint 
    SELECT @LastRID= MAX(Id) FROM Receipts
    INSERT INTO Receipts(<col list>)
    SELECT <col list> FROM Outgoing WHERE Receipt_Id IS NULL
    

    CHECKSUM

    update O
    set O.Receipt_Id=R.Id
    From Outgoing  O
    Join Receipts R 
    on CHECKSUM(o.<col list>)=CHECKSUM(R.<col list>)
    and R.Id>@LastRID