[SQL] 19. 레코드에서 필드로의 갱신1 (필드값을 속성으로 만들기1)

레코드에서 필드로의 갱신

이번 포스팅에서는 2개의 테이블을 활용하여 한 쪽 테이블의 필드값을 다른 테이블의 속성 값으로 가져와 갱신하는 예제를 살펴볼 것이다.

 

 

 

 

 

- 테이블 만들기

우선 필드값이 들어있는 ScoreRows 테이블과 갱신하여 채울 ScoreCols 테이블을 생성해보도록 한다.

 

CREATE TABLE ScoreRows(
        student_id VARCHAR(10),
        subject VARCHAR(10),
        score INT(10));
 
INSERT INTO ScoreRows VALUES('A001', '영어', 100);
INSERT INTO ScoreRows VALUES('A001', '국어', 58);
INSERT INTO ScoreRows VALUES('A001', '수학', 90);
INSERT INTO ScoreRows VALUES('B002', '영어', 77);
INSERT INTO ScoreRows VALUES('B002', '국어', 60);
INSERT INTO ScoreRows VALUES('C003', '영어', 52);
INSERT INTO ScoreRows VALUES('C003', '국어', 49);
INSERT INTO ScoreRows VALUES('C003', '사회', 100);
 
SELECT * FROM ScoreRows;

[ ScoreRows 테이블 생성 쿼리 ]

 

 

CREATE TABLE ScoreCols(
        student_id VARCHAR(10),
        score_en VARCHAR(10),
        score_kr VARCHAR(10),
        score_mt VARCHAR(10));
        
INSERT INTO ScoreCols VALUES('A001',NULL,NULL,NULL);
INSERT INTO ScoreCols VALUES('B002',NULL,NULL,NULL);
INSERT INTO ScoreCols VALUES('C003',NULL,NULL,NULL);
 

[ ScoreCols 테이블 생성 쿼리 ]

 

 

[ 생성된 테이블 ( L : ScoreRows / R : ScoreCols )]

 

예제에 사용할 두 테이블을 위와 같이 만들었다. 

위의 두 테이블을 활용하여 ScoreCols(오른쪽 테이블)의 NULL 값을 채워보도록 할 것이다.

 

 

 

 

 

- 테이블 갱신 쿼리 작성하기

이제 ScoreRows 테이블 안의  subject 속성의 필드값을 활용하여, ScoreCols 테이블의 속성으로 만들어 주고 해당하는 score을 각 필드 값으로 채워주는 쿼리를 작성해 볼 것이다.

 

필자는 CASE 구문과 서브쿼리를 활용한 테이블 갱신 쿼리를 작성해보았다.

 

 

그런데...

UPDATE ScoreCols
    SET (score_en, score_kr, score_mt)
        =(SELECT MAX(CASE WHEN subject='영어'
                                 THEN score
                                 ELSE NULL END) AS score_en,
                      MAX(CASE WHEN subject='국어'
                                 THEN score
                                 ELSE NULL END) AS score_kr,
                     MAX(CASE WHEN subject='수학'
                                 THEN score
                                 ELSE NULL END) AS score_mt
          FROM ScoreRows
          WHERE ScoreRows.student_id = ScoreCols.student_id);

[ ScoreRows, ScoreCols 테이블을 활용한 갱신 쿼리 - MySQL ]

 

 

에러가 난다.  쿼리상으로 이상이 없어보여 한참 헤매었는데, 오류의 원인은 쿼리에 존재하지 않았다.

 

 

위에 코드를 보면, UPDATE 갱신을 할 때 "SET (score_en, score_kr, score_mt)" 처럼 필드를 한번에 묶어주는 리스트 형식으로 지정하여 활용하였는데 이 방식을 MySQL이 지원을 하지 않는다고 한다. 필드를 리스트 형식으로 활용하는 방법은 ORACLE, DB2만 지원한다고 한다.

 

 

 

 

최근에 MySQL과 HeidiSQL을 설치한 것이 무색하게되었다.

결국 과거에 사용했던 ORACLE Live SQL을 활용하여 쿼리를 돌려보았다.

 

[ ScoreRows, ScoreCols 테이블을 활용한 갱신 쿼리 - ORACLE ]

 

쿼리가 문제없이 잘 돌아간다.

(참고로 테이블을 만들때 MySQL 쿼리를 똑같이 활용하되, 컬럼 형식을 VARCHAR -> VARCHAR2 , INT -> NUMBER 로 변경해서 만들어주어야 하는 것을 잊지 말도록 하자.)

 

 

 

 

 

 

 

리스트 형식을 사용하지 않고 MySQL에서 테이블 갱신을 해결하는 방법도 어렵지 않다.(다만 위의 쿼리보다 비효율적일 뿐이다.)

MySQL을 활용한 예제의 테이블 갱신은 다음 포스팅에서 계속 진행해보도록 하자.