데이터베이스를 사랑하는 사람들의 모임 데이터베이스 사랑넷

MS-SQL Q&A 6787 게시물 읽기

No. 6787

전화번호 번호이동에 따른 전화번호 업데이트 관련 문의입니다.
작성자	골프	작성일	2014-02-07 05:55ⓒ 2014-02-07 05:56ⓜ	조회수	9,462

010으로 번호가 변경되면서 고객측으로 부터 들어온 요청인데요. 기존 011, 016,017,018,019 번호를 일괄 업데이터 해달라는 요구입니다.

변경되는 형식은 아래와 같은데요.

만약 member 테이블에 hp 필드가 있다고 할때.. 이거를 한번에 일괄 업데이트 할수 있는가요???

할수 있다면 어떻게 할수 있는지 고수님들 도움 부탁드립니다.

===========================================================

전환용 번호 안내 테이블

기존 이동전화 국번호    전환 010 국번호      비고
011-200~499             010-5200~5499     국번호 앞에 한자리 추가
011-500~899               010-3500~3899
011-9000~9499            010-9000-9499        기존번호 유지
011-9500~9999            010-8500~8999       국번호 앞에 한자리 변경
011-1700~1799            010-7100~7199       국번호 앞에 두자리 변경

017-200~499 010-6200~6499 국번호 앞에 한자리 추가
017-500~899 010-4500~4899

016-200~499               010-3200~3499        국번호 앞에 한자리 추가
016-500~899               010-2500~2899
016-9000~9499            010-7000~7499        국번호 앞에 한자리 변경
016-9500~9999            010-9500~9999        기존번호 유지

018-200~499               010-4200~4499        국번호 앞에 한자리 추가
018-500~899               010-6500~6899

019-200~499               010-2200~2499        국번호 앞에 한자리 추가
019-500~899               010-5500~5899
019-9000~9499            010-8000~8499        국번호 앞에 한자리 변경
019-9500~9999            010-7500~7999

이 글에 대한 댓글이 총 6건 있습니다.

-- 대충 이런 테이블이 있다손 치고요...

CREATE TABLE #tt(

uu INT IDENTITY(1,1)

, hp VARCHAR(15)

)

;

-- 이런 핸드폰번호들이 있다고 치면

INSERT INTO #tt( hp ) VALUES( '011-200-0001' ), ('011-500-0002'), ('011-9000-0003')

;

WITH upinfo

(

-- 변경할 룰을 넣고

SELECT '011' AS cc, 200 AS ff, 499 AS tt, '5' AS md

UNION ALL SELECT '011', 500, 899, '3'

UNION ALL SELECT '011', 9000, 9499, ''

UNION ALL SELECT '011', 9500, 9999, '8'

)

, nums

(

-- 전체 범위 중에

SELECT TOP 10000 ROW_NUMBER() OVER ( ORDER BY ( SELECT NULL ) ) AS num

FROM sys.all_columns AS aa

CROSS JOIN sys.all_columns AS bb

)

, upinfoExt

(

-- 범위에 맞게 늘어트리고

SELECT *

FROM upinfo

CROSS JOIN nums

)

-- 아래 테이블을 기준으로 update문 작성 고고씽~~

SELECT uu, hp, new + RIGHT( hp, LEN(hp)-ll+1)

FROM #tt AS tt

INNER JOIN

(

SELECT *, cc + '-' + CAST( num AS VARCHAR(4) ) AS old

, LEN( '010-' + md ) AS ll

, '010-' + md AS new

FROM upinfoext

WHERE ff <= num AND num <= tt

) AS bb

ON LEFT( tt.hp, CHARINDEX( '-', hp, CHARINDEX( '-', hp )+1 )-1 ) = bb.old

;

우욱님이 2014-02-07 09:46에 작성한 댓글입니다. Edit Ｘ

답변 감사합니다.

응용해서 해보겠습니다. ^^

골프님이 2014-02-07 17:01에 작성한 댓글입니다. Edit Ｘ

죄송합니다. 다시 질문드립니다.

환경을 말씀안드려서 그렇네용...

MS-SQL 2000 을 사용중인데요.. 위 쿼리로는 에러가 나는데.. 어떻게 해야 하나요??

그리고 update도 어디서 적어야 하는지요??

쿼리로 한번 실행해서 update까지 되게 할수 있는지요???

골프님이 2014-02-08 07:56에 작성한 댓글입니다. Edit Ｘ

-- CTE를 제거한 버전을 만들어 봤는데 2000에서도 돌아가긴 할거 같은데 그리운 2000 이거 하자고 설치하긴 좀 그래서.. ㅋ

-- 대충 이런 테이블이 있다손 치고요...

CREATE TABLE #tt(

uu INT IDENTITY(1,1)

, hp VARCHAR(15)

)

;

-- 이런 핸드폰번호들이 있다고 치면

INSERT INTO #tt( hp ) VALUES( '011-200-0001' ), ('011-500-0002'), ('011-9000-0003')

;

-- 아래 테이블을 기준으로 update문 작성 고고씽~~

SELECT *

FROM #tt

;

-- 업데이트가 되는 걸 보시려면 아래의 주석을 푸세요 쬬 아래의 dd 어쩌고에 붙은 것 까지도

--UPDATE #tt

--SET hp = dd.newHp

--FROM #tt

--INNER JOIN

--(

SELECT uu, hp, REPLACE( new + RIGHT( hp, LEN(hp)-ll+1), '--', '-' ) AS newHp

FROM #tt AS tt

INNER JOIN

(

SELECT *, cc + '-' + CAST( num AS VARCHAR(4) ) AS old

, LEN( '010-' + md ) AS ll

, '010-' + md AS new

FROM (

SELECT *

FROM (

SELECT '011' AS cc, 200 AS ff, 499 AS tt, '5' AS md

UNION ALL SELECT '011', 500, 899, '3'

UNION ALL SELECT '011', 9000, 9499, ''

UNION ALL SELECT '011', 9500, 9999, '8'

) AS upinfo

CROSS JOIN (

SELECT TOP 10000 ROW_NUMBER() OVER ( ORDER BY ( SELECT NULL ) ) AS num

FROM sys.all_columns AS aa

CROSS JOIN sys.all_columns AS bb

) AS nums

) AS uniinfoExt

WHERE ff <= num AND num <= tt

) AS bb

ON LEFT( tt.hp, CHARINDEX( '-', hp, CHARINDEX( '-', hp )+1 )-1 ) = bb.old

--) AS dd

-- ON #tt.hp = dd.hp

;

SELECT *

FROM #tt

;

우욱님이 2014-02-08 21:15에 작성한 댓글입니다. Edit Ｘ

우욱님 염치없지만 다시 질문드립니다.

ROW_NUMBER()는 MS-SQL2000에서는 안되는가요??

오류가 발생하는데... 이거 해결할수 있는 방법이 있을까요??

골프님이 2014-02-13 09:19에 작성한 댓글입니다.
이 댓글은 2014-02-13 09:20에 마지막으로 수정되었습니다. Edit Ｘ

-- row_number() 2000때는 없었구나.. ㅋㅋ

SELECT TOP 10000 IDENTITY(INT, 1, 1 ) AS num

INTO #nums

FROM master.dbo.syscolumns AS cc1

CROSS JOIN master.dbo.syscolumns AS cc2

;

-- 대충 이런 테이블이 있다손 치고요...

CREATE TABLE #tt(

uu INT IDENTITY(1,1)

, hp VARCHAR(15)

)

;

-- 이런 핸드폰번호들이 있다고 치면

INSERT INTO #tt( hp ) VALUES( '011-200-0001' ), ('011-500-0002'), ('011-9000-0003')

;

-- 아래 테이블을 기준으로 update문 작성 고고씽~~

SELECT *

FROM #tt

;

-- 업데이트가 되는 걸 보시려면 아래의 주석을 푸세요 쬬 아래의 dd 어쩌고에 붙은 것 까지도

--UPDATE #tt

--SET hp = dd.newHp

--FROM #tt

--INNER JOIN

--(

SELECT uu, hp, REPLACE( new + RIGHT( hp, LEN(hp)-ll+1), '--', '-' ) AS newHp

FROM #tt AS tt

INNER JOIN

(

SELECT *, cc + '-' + CAST( num AS VARCHAR(4) ) AS old

, LEN( '010-' + md ) AS ll

, '010-' + md AS new

FROM (

SELECT *

FROM (

SELECT '011' AS cc, 200 AS ff, 499 AS tt, '5' AS md

UNION ALL SELECT '011', 500, 899, '3'

UNION ALL SELECT '011', 9000, 9499, ''

UNION ALL SELECT '011', 9500, 9999, '8'

) AS upinfo

CROSS JOIN #nums

) AS uniinfoExt

WHERE ff <= num AND num <= tt

) AS bb

ON LEFT( tt.hp, CHARINDEX( '-', hp, CHARINDEX( '-', hp )+1 )-1 ) = bb.old

--) AS dd

-- ON #tt.hp = dd.hp

;

SELECT *

FROM #tt

우욱님이 2014-02-13 13:20에 작성한 댓글입니다. Edit Ｘ

[Top]

No.	제목	작성자	작성일	조회
6791	mssql 에서 채번? PK코드 값을 자동으로 넣고 싶습니다. [4]	풀잎	2014-03-12	12780
6790	테이블 데이터 삭제 후 조회. [3]	초보	2014-03-12	8798
6789	한 테이블 동시 접근에 관하여 질문 드려요! [4]	이성원	2014-02-28	11363
6787	전화번호 번호이동에 따른 전화번호 업데이트 관련 문의입니다. [6]	골프	2014-02-07	9462
6785	IP접속 불가 [1]	신승범	2014-02-04	8646
6784	group by 에서 최근값 가져오기 [1]	둥둥이	2014-01-23	9388
6783	UPDATE 트리거 [3]	DB	2014-01-23	9066