使用光标更新单个表中的行 [英] Using a Cursor to Update Rows in Single Table

问题描述

我正在寻求指导使用MS SQL游标或SQL while循环合并（添加，更新和设置非活动）行使用单个表，基于以下条件导致寻求最终的数据集：

---

TABLE VIEW（SELECT * FROM TABLE WHERE ENTITY = 123（@ID）

ENTITY     ENTITY_TYPE     VALUE    STATUS_TYPE

123    1    1    1

123     1    4    1

123    1    9    1

TABLE VIEW（SELECT * FROM TABLE WHERE ENTITY = 456（@OverrideID）

ENTITY     ENTITY_TYPE     VALUE     STATUS_TYPE

456    1    1    1

456    1    5    1

最终数据集如下：

ENTITY     TYPE     VALUE     STATUS_TYPE

123    1    1    3

123    1    4    3

123    1    9    3

456    1    1    1

456    1    4    1

456    1    9    1

456    1    1    1

456    1    5    1



- 检查并比较每一行

- 如果@OverrideID = @ID（MATCH）

-----将@ID设置为无效保留副本

- 如果@OverrideID！= @ID（NOT MATCH）

-----插入@带有@OverrideID（COPY）的ID数据

-----将@ID设置为非活动。

我开始写以下内容并需要帮助。供参考，@ID = 123和@OverrideID = 456

---

  DECLARE @ ENTITY BIGINT，@ENTITY_TYPE BIGINT，@VALUE BIGINT，@ E1 BIGINT，@ T1 BIGINT，@ V1 BIGINT 
 DECLARE type_cursor CURSOR LOCAL FAST_FORWARD FOR 
 SELECT * FROM TypeValue WHERE ENTITY = @ID 
 
 SET NOCOUNT OFF 
 OPEN type_cursor 
 
 FETCH NEXT FROM type_cursor INTO @OverrideID，@ID，@ENTITY，@ENTITY_TYPE，@VALUE 
 
 WHILE（@ @FETCH_STATUS = 0）
 BEGIN 
 
 SELECT @ENTITY，@ENTITY_TYPE，@VALUE 
 
如果@VALUE不为NULL 
 BEGIN 
 SELECT @ T1 = @ENTITY_TYPE，@ V1 = @VALUE 
 END 
 ELSE 
 BEGIN 
 UPDATE TypeValue 
 SET ENTITY = @OverrideID，ENTITY_TYPE = @ T1，VALUE = @ V1 WHERE ENTITY = @ID 
 END 
 
 FETCH NEXT FROM type_cursor INTO @OverrideID，@ID，@ENTITY，@ENTITY_TYPE，@VALUE 
 
 END 
 
 CLOSE type_cursor 
 DEALLOCATE type_cursor 
 SET NOCOUNT OFF 
  

解决方案

最终结果集看起来像

  select entity，entity_type，value，3 status_type 
 from [table] 
其中entity = @id 
 union all 
 select @ override，entity_type，value，status_type 
 from [table] 
其中entity = @id 
 union all 
 select entity，entity_type，value，status_type 
 from [table] 
其中entity = @override 
  pre> 
I am seeking guidance using MS SQL cursor or SQL while loop to merge (add, update, and set inactive) rows using single table based on  criteria below resulting in seeking the final dataset:

 

TABLE VIEW (SELECT * FROM TABLE WHERE ENTITY = 123 (@ID)



ENTITY    ENTITY_TYPE      VALUE           STATUS_TYPE 

123          1                1               1

123          1                4               1

123          1                9               1




TABLE VIEW (SELECT * FROM TABLE WHERE ENTITY = 456 (@OverrideID)

ENTITY    ENTITY_TYPE      VALUE           STATUS_TYPE

456          1                1              1

456          1                5              1



Final Data Set below:


ENTITY   TYPE       VALUE           STATUS_TYPE 

123     1     1     3

123     1        4     3

123     1     9     3

456     1     1     1

456     1     4     1

456     1     9     1

456     1     1     1

456     1     5    1



-- Check and compare each row 

-- IF @OverrideID = @ID (MATCH) 

----- Set @ID to inactive keeping copy

-- If @OverrideID != @ID (NOT MATCH)

----- Insert @ID data with @OverrideID (COPY)

----- Set @ID to inactive.

I began writing the following and need help. For reference, @ID = 123 and @OverrideID = 456

 

DECLARE @ENTITY BIGINT, @ENTITY_TYPE BIGINT, @VALUE BIGINT, @E1 BIGINT, @T1 BIGINT, @V1 BIGINT
DECLARE type_cursor CURSOR LOCAL FAST_FORWARD FOR
SELECT * FROM TypeValue WHERE ENTITY = @ID

SET NOCOUNT OFF
OPEN type_cursor

FETCH NEXT FROM type_cursor INTO @OverrideID, @ID, @ENTITY, @ENTITY_TYPE, @VALUE

WHILE (@@FETCH_STATUS = 0)
    BEGIN

        SELECT @ENTITY, @ENTITY_TYPE, @VALUE

     IF @VALUE IS NOT NULL
        BEGIN
        SELECT @T1 = @ENTITY_TYPE, @V1 = @VALUE
        END
    ELSE
    BEGIN
    UPDATE TypeValue
    SET ENTITY = @OverrideID, ENTITY_TYPE = @T1, VALUE = @V1 WHERE ENTITY = @ID
    END

FETCH NEXT FROM type_cursor INTO @OverrideID, @ID, @ENTITY, @ENTITY_TYPE, @VALUE

END

CLOSE type_cursor 
DEALLOCATE type_cursor 
SET NOCOUNT OFF 

解决方案
That final result set looks like
    select entity,entity_type,value,3 status_type
      from [table]
     where entity = @id
 union all
    select @override,entity_type,value,status_type
      from [table]
     where entity = @id
 union all
    select entity,entity_type,value,status_type
      from [table]
     where entity = @override


                        
这篇关于使用光标更新单个表中的行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！