연속 번호 지정으로 SQL 업데이트

연속 번호 지정으로 SQL 업데이트

저는 1로 시작하는 연속 번호표를 업데이트하고 싶습니다.업데이트에 where 절이 있으므로 절에 맞는 결과만 번호가 다시 지정됩니다.임시 테이블을 사용하지 않고 효율적으로 이 작업을 수행할 수 있습니까?

이는 데이터베이스에 따라 다를 수 있지만 변수를 사용하는 MySQL 5에 대한 솔루션은 다음과 같습니다.

SET @a:=0;
UPDATE table SET field=@a:=@a+1 WHERE whatever='whatever' ORDER BY field2,field3

그러나 질문을 편집하고 사용 중인 데이터베이스를 표시해야 합니다.

편집: SQL Server용 T-SQL을 활용한 솔루션을 찾았습니다.MySQL 방법과 매우 유사합니다.

DECLARE @myVar int
SET @myVar = 0

UPDATE
  myTable
SET
  @myvar = myField = @myVar + 1

Microsoft SQL Server 2005/2008. ROW_NUMBER() 함수는 2005년에 추가되었습니다.

; with T as (select ROW_NUMBER() over (order by ColumnToOrderBy) as RN
        , ColumnToHoldConsecutiveNumber from TableToUpdate
    where ...)
update T
set ColumnToHoldConsecutiveNumber = RN

편집: SQL Server 2000의 경우:

declare @RN int
set @RN = 0 

Update T
set ColumnToHoldConsecutiveNubmer = @RN
    , @RN = @RN + 1
where ...

참고: 제가 테스트했을 때 @RN의 증가는 열을 @RN으로 설정하기 전에 발생하는 것으로 보였기 때문에 위의 수치는 1부터 시작합니다.

편집: 테이블 내에 여러 개의 순차 번호를 만들고 싶어하는 것 같습니다.요구 사항에 따라 SQL Server 2005/2008에서 단일 경로로 다음을 추가할 수 있습니다.partition by에게over절:

; with T as (select ROW_NUMBER() 
        over (partition by Client, City order by ColumnToOrderBy) as RN
     , ColumnToHoldConsecutiveNumber from TableToUpdate)
update T
set ColumnToHoldConsecutiveNumber = RN

새 PrimaryKey 열을 생성하려면 다음과 같이 하십시오.

ALTER TABLE accounts ADD id INT IDENTITY(1,1) 

CTE 또는 WITH를 사용할 뿐만 아니라 동일한 테이블에 자체 조인하여 업데이트를 사용할 수도 있습니다.

UPDATE a
SET a.columnToBeSet = b.sequence
FROM tableXxx a
INNER JOIN
(
   SELECT ROW_NUMBER() OVER ( ORDER BY columnX ) AS sequence, columnY, columnZ
   FROM tableXxx
   WHERE columnY = @groupId AND columnY = @lang2
) b ON b.columnY = a.columnY AND b.columnZ = a.columnZ

파생 테이블인 별칭 b는 ROW_NUMBER() 함수를 통해 가상 기본 키를 구성하는 다른 일부 열과 함께 시퀀스를 생성하는 데 사용됩니다.일반적으로 각 행에는 고유한 시퀀스 값이 필요합니다.

WHERE 절은 선택 사항이며 지정된 조건을 충족하는 행으로 업데이트를 제한합니다.

그런 다음 파생된 테이블이 동일한 테이블, 별칭 a에 결합되어 업데이트할 열이 생성된 시퀀스로 설정된 가상 기본 키 열에 결합됩니다.

오라클에서는 다음과 같이 작동합니다.

update myTable set rowColum = rownum
where something = something else

http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/pseudocolumns009.htm#i1006297

Shannon이 완전히 작동하는 예를 얻기 위해 저는 그의 답변을 편집해야 했습니다.

; WITH CTE AS (
    SELECT ROW_NUMBER() OVER (ORDER BY [NameOfField]) as RowNumber, t1.ID
    FROM [ActualTableName] t1
)
UPDATE [ActualTableName]
    SET Name = 'Depersonalised Name ' + CONVERT(varchar(255), RowNumber)
FROM CTE
    WHERE CTE.Id = [ActualTableName].ID

그의 대답은 T를 업데이트하려고 했기 때문에, 그의 경우는 공통 테이블 표현식의 이름이었고, 그것은 오류를 던집니다.

UPDATE TableName
SET TableName.id = TableName.New_Id
FROM (
  SELECT id, ROW_NUMBER() OVER (ORDER BY id) AS New_Id
  FROM TableName
  ) TableName

저는 이 기술을 서수와 순차적으로 번호가 매겨진 열을 채우기 위해 수년간 사용해 왔습니다.하지만 최근 SQL Server 2012에서 실행할 때 문제가 발생했습니다.쿼리 엔진이 내부적으로 여러 스레드를 사용하여 업데이트를 적용하고 있으며 UPDATE의 술어 부분이 스레드 안전한 방식으로 처리되지 않는 것으로 보입니다.다시 작동하기 위해 SQL Server의 최대 병렬화 수준을 1코어로 다시 구성해야 했습니다.

EXEC sp_configure 'show advanced options', 1;
GO
RECONFIGURE WITH OVERRIDE;
GO
EXEC sp_configure 'max degree of parallelism', 1;
GO
RECONFIGURE WITH OVERRIDE;
GO

DECLARE  @id int
SET @id = -1
UPDATE dbo.mytable
SET @id = Ordinal = @id + 1

이렇게 하지 않으면 대부분의 순차 번호가 표 전체에서 중복된다는 것을 알 수 있습니다.

원하는 결과를 얻을 수 있는 또 다른 방법

시퀀스 개체 생성 - (https://learn.microsoft.com/en-us/sql/t-sql/statements/create-sequence-transact-sql?view=sql-server-ver16)

 CREATE SEQUENCE dbo.mySeq
        AS BIGINT
        START WITH 1 -- up to you from what number you want to start cycling
        INCREMENT BY 1 -- up to you how it will increment
        MINVALUE 1
        CYCLE
        CACHE 100;

기록 업데이트

UPDATE TableName
SET Col2 = NEXT VALUE FOR dbo.mySeq
WHERE ....some condition...

EDIT: 다음에 사용할 때 1부터 시작하도록 시퀀스를 재설정합니다.

ALTER SEQUENCE dbo.mySeq RESTART WITH 1 -- or start with any value you need`

숫자 표에 가입하시겠습니까?여분의 표가 필요하지만, 일시적인 것은 아닙니다. 여러분은 실용적으로 숫자표를 보관할 것입니다.

http://web.archive.org/web/20150411042510/http ://sqlserver2000.databases.aspfaq.com/why-should-i-consider-using-an-auxiliary-numbers-table.html 참조

또는

http://www.sqlservercentral.com/articles/Advanced+Querying/2547/

(후자는 무료 등록이 필요하지만 MS SQL Server에 대한 팁과 기술의 매우 좋은 소스이며 SQL 구현에는 많은 것이 적용됩니다.)

이 단일 명세서 솔루션이 도움이 되기를 바랍니다.표에 열 ID가 있다고 가정합니다.테이블, 정수 열 및 절 열 자리 표시자를 바꿔야 합니다.

UPDATE {{table-abc}} a 
SET {{your-integer-column}} = (
  SELECT COUNT(id)+1 
  FROM {{table-abc}} 
  WHERE {{your-where-clause-column}} = {{a.your-where-clause-column}} 
  AND id > a.id 
);

가능하지만 매우 복잡한 일부 쿼리를 통해서만 가능합니다. 기본적으로 지금까지 선택한 레코드 수를 세고 시퀀스 ID로 사용하는 하위 쿼리가 필요합니다.저는 한 때 비슷한 것을 썼습니다 - 효과가 있었지만, 그것은 많은 고통이었습니다.

솔직히, 당신은 자동 증분 필드가 있는 임시 테이블이 더 나을 것입니다.

언급URL : https://stackoverflow.com/questions/1167885/update-sql-with-consecutive-numbering