时间戳

首页 > 解决方案 > 每次添加值加 1 作为更新

sql - 每次添加值加 1 作为更新

问题描述

在 Advantage 中尝试更新临时表中的值，但每次都加 1。起始值需要比另一个表的最大值大一，并且每次更新新行时，都会添加一个。忽略除顶部的声明游标和@nevid 字符串之外的所有内容。我可以让它填充我的临时表中的 evid 列，但它会为每条记录添加 71，这是正确的下一个数字，但我需要它是 71、72、73 等。我哪里出错了？

DECLARE cur CURSOR;
DECLARE @nevid INTEGER;
DECLARE @startdate string;
DECLARE @starttime string;
DECLARE @expectedenddate string;
DECLARE @expectedendtime string;
DECLARE @enddate string;
DECLARE @endtime string;
@nevid = 
( 
       SELECT (max(evid)+1) 
       FROM   pcplevnt
);

SELECT * 
INTO   #tmpev 
FROM   <table>;open cur 
AS 
  SELECT * 
  FROM   #tmpev;
    WHILE
        FETCH cur do 
        UPDATE #tmpev 
        SET evid = cast(@nevid AS sql_char(4));SET @nevid = @nevid + 1;
    END WHILE;
close cur;

标签： sqlloopsadvantage-database-server

解决方案

我已经构建了一个 MVCE 来重现和解决您的问题：

DECLARE cur    CURSOR;
DECLARE @nevid INTEGER;

TRY DROP TABLE #pcplevnt; CATCH ALL END TRY; 
TRY DROP TABLE #tmpev;    CATCH ALL END TRY; 

CREATE TABLE
  #pcplevnt
(
  evid INTEGER
);

DELETE FROM #pcplevnt;
INSERT INTO #pcplevnt (evid) SELECT 111 FROM system.iota;

SET @nevid = (
SELECT
  max(evid) + 1
FROM #pcplevnt
);

CREATE TABLE
  #tmpev
(
  id AUTOINC,
  evid NVARCHAR(4)
);

INSERT INTO
  #tmpev
(
  evid
)
      SELECT '1' FROM system.iota
UNION SELECT '2' FROM system.iota
UNION SELECT '3' FROM system.iota
;

OPEN cur AS SELECT * FROM #tmpev;

WHILE FETCH cur DO
  UPDATE
    #tmpev
  SET
    evid = CAST(@nevid AS SQL_CHAR(4))
  WHERE
    id = cur.id
  ;
  SET @nevid = @nevid + 1;
END WHILE;

CLOSE cur;

select * from #tmpev;

注意循环内部的WHERE条件：UPDATE

  UPDATE
    #tmpev
  SET
    evid = CAST(@nevid AS SQL_CHAR(4))
  WHERE
    id = cur.id
  ;
  SET @nevid = @nevid + 1;

我添加了一个主键字段id，我可以在循环内进行比较，以便一次只更新临时表的一行。

推荐阅读