[데이터 모델과 SQL] 15. 저장된 정보 수정 - UPDATE

 

데이터 관리를 CRUD(Create, Read, Update, and Delete)로 요약할 수 있다. SQL 작성에서 Read를 위한 검색식 작성이 대부분이지만 CUD를 위한 요청식도 반드시 살펴볼 필요가 있다.

 

데이터베이스에 저장되어 있는 데이터들에 수정이 필요할 때 UPDATE 명령을 사용한다.
문법: UPDATE <테이블 이름> SET <컬럼 이름> = <값 | DEFAULT | NULL> [WHERE <조건>]

 

만약 컬럼과 값의 쌍이 둘 이상이라면 SET 키워드 뒤에 <컬럼 이름>=<값|DEFAULT|NULL>을 콤마(,)로 연결해준다. 예를 들면 아래와 같다.

예) 변경하고자 하는 컬럼이 둘 이상인 경우

UPDATE customers SET custFirstName='Tom', custLastName='Baker' WHERE customerID=25;

 

SET 구문에서 = 기호 왼쪽과 오른쪽의 컬럼이 같을 때는 변경 이전 값을 참조해서 변경한 후 같은 이름의 컬럼에 변경한 값을 입력한다.

 

예) 소매 가격(retail price)을 10% 올린다.
수정하는 값은 RetailPrice + (RetailPrice*0.1)가 된다.

UPDATE products SET RetailPrice = RetailPrice + (RetailPrice*0.1);

 

예) 의류(Clothing) 가격을 10% 올린다.
먼저 의류에 해당하는 상품을 알아야 한다.

SELECT CategoryID FROM categories WHERE CategoryDescription = 'Clothing';

이제 위 상품 분류에 해당하는 상품들의 가격을 10% 올린다.

UPDATE products 
SET products.RetailPrice = products.RetailPrice + products.RetailPrice*0.1 
WHERE products.CategoryID=
(SELECT CategoryID FROM categories WHERE CategoryDescription='Clothing');

 

조금 더 복잡한 예를 한 번 다루어보자.

예) 모든 소묘(drawing) 수업들의 강의실을 1635호로 변경하고 수업 요일을 월수금(Monday-Wednesday-Friday)에서 화목토(Tuesday-Thursday-Saturday)로 변경한다.

① 먼저 소묘 수업의 정보를 얻는다. 이 정보를 얻기 위해 교과목(subjects) 테이블과 수업(classes) 테이블을 연결한다.

SELECT classes.ClassID 
FROM classes LEFT OUTER JOIN subjects ON classes.SubjectID=subjects.SubjectID 
WHERE subjects.SubjectName LIKE '%Draw%' 
OR subjects.SubjectName LIKE '%draw%'

② 위 수업들의 강의실(ClassRoomID)을 1635로 그리고 수업 시간을 화목토로 변경한다.

UPDATE classes SET ClassRoomID=1635, MondaySchedule=0, TuesdaySchedule=1, WednesdaySchedule=0, ThursdaySchedule=1, FridaySchedule=0, SaturdaySchedule=1 
WHERE ClassID IN (SELECT classes.ClassID FROM classes LEFT OUTER JOIN subjects ON classes.SubjectID=subjects.SubjectID WHERE subjects.SubjectName LIKE '%Draw%' OR subjects.SubjectName LIKE '%draw%');

 

★★ 데이터베이스의 내용을 한 번 수정하면 이전 값으로 되돌리기 어렵다. 내용을 수정하기 전에 SELECT 명령으로 수정 대상들과 기존 값들을 먼저 확인해보는 것을 추천한다.

 

● 서브 쿼리를 이용한 수정 값 계산

예) 주문(orders) 테이블의 총 주문 금액(OrderTotal)을 최신 정보로 반영하라.

주문 당 상품 및 주문량(QuantityOrdered) 그리고 단가(QuotedPrice)가 주문 상세(orderdetails) 테이블에 기록되어 있다. 먼저 주문 당 주문 금액을 구해보자.

SELECT orders.OrderNumber, 
(SELECT SUM(QuantityOrdered*QuotedPrice) FROM orderdetails WHERE orders.OrderNumber=orderdetails.OrderNumber) 
FROM orders;

위 검색식의 내용을 이용하면 주문 당 총 주문 금액을 계산하고 그 내용을 주문 테이블에 반영할 수 있다.

UPDATE orders 
SET OrderTotal=(SELECT SUM(QuantityOrdered*QuotedPrice) FROM orderdetails WHERE orders.OrderNumber=orderdetails.OrderNumber);

 

---

 

● Transaction
데이터를 변경하면 원래 내용으로 되돌리기 어렵다. 따라서 데이터를 수정할 때는 신중해야 한다. 데이터를 수정하면 그 영향이 크기 때문에 SQL 표준에서는 거래(transaction) 개념을 정의하고 있다.
Transaction 개념 아래서 하나의 거래를 주문 - 송금 - 배송 - 상품수령 - 구매결정의 과정으로 세분화하고 세부 과정들이 모두 완결되어야 하나의 거래가 완결된다. 이와 마찬가지로 데이터베이스에서 Transaction을 시작하면(START TRANSACTION) 데이터베이스의 내용을 수정하기 위해 몇 가지 단계를 거치고 최종적으로 반영(COMMIT) 과정을 거쳐서 내용 수정이 완료된다.
만약 변경 과정을 중단하고 원래 내용으로 돌아가고 싶을 때는 복구(ROLLBACK) 명령을 사용하게 된다.

Transaction은 사용자가 명시적으로 시작해야 하는 경우도 있고, DBMS가 묵시적으로 적용해 줄 때도 있다. 이는 데이터베이스 구현과 관련된 주제이기 때문에 그 동작이 DBMS마다 다르다.

 

● Trigger
테이블의 컬럼들에 다른 컬럼 혹은 다른 테이블의 값을 참조하여 계산된 결과가 포함되는 것은 바람직하지 못하다. 왜냐하면 저장된 계산 결과가 최신 데이터를 반영하지 못할 수 있기 때문이다.
하지만 부득이 계산 값을 기록하는 컬럼을 사용하게 될 경우 참조하는 데이터에 변화가 생기면 계산 값을 다시 산출하도록 할 수 있다. 이와 같은 기능을 트리거Trigger라고 부른다. 트리거 기능은 SQL 표준에서 반드시 구현하도록 정의하고 있지 않기 때문에 사용하는 DBMS에 그 지원 여부가 달려있다.

 

■