MySQL에서 ROW_NUMBER, ROWNUM처럼 번호 매기는 방법, 구한 것을 UPDATE 하기

MSSQL의 ROW_NUMBER나 ORACLE의 ROWNUM처럼 MySQL에서 로우에 번호를 매기는 방법

# @rownum을 초기화하고 사용한다.
set @rownum:=0; 
select @rownum:=@rownum+1, id, newid from tmp;

MySQL ROWNUM 결과

 

이와 같이 rownum을 구한 것을 newid에 적용하는 방법, UPDATE 하기

update tmp t1
  inner join
  (
   select @rownum:=@rownum+1 as 'rowNum', id
   from tmp2, (SELECT @rownum := 0) rn
   #order by orderNum asc
  ) t2
   on t1.id= t2.id
set t1.newid = t2.rowNum;

select id, newid from tmp;

MySQL 하나의 칼럼을 ROWNUM으로 UPDATE

 

이때 테이블이 임시테이블이고 위의 예제에서 tmp2 대신 tmp, 즉 하나의 테이블에서 업데이트 및 조인을 하려고 하면

Can't reopen tableError Code: 1137. 오류가 발생한다.

임시 테이블은 동시에 두 군데 이상에서 참조를 할 수 없기 때문이다.

 

# 임시테이블1 tmp
DROP TEMPORARY TABLE IF EXISTS tmp; 
CREATE TEMPORARY TABLE tmp ( id int NOT NULL AUTO_INCREMENT PRIMARY KEY, newid int, login_nid bigint, fcm_token varchar(300), notype varchar(10));

# 임시테이블2 tmp2
DROP TEMPORARY TABLE IF EXISTS tmp2; 
CREATE TEMPORARY TABLE tmp2 ( id int);

insert into tmp2 select id from tmp;

따라서 임시테이블의 한 칼럼을 ROWNUM으로 UPDATE하고자 할 때에는 임시테이블을 하나 더 만들어서 복제한 다음 사용한다.