Oracle :: UPDATE, DELETE

(파일명 20231101_01_scott)

■ UPDATE

- 테이블에서 기존 데이터를 수정(변경)하는 구문

- 형식 및 구조

→ UPDATE 테이블명

     SET 컬럼명 = 변경할값[, 컬럼명 = 변경할값,....] 

     [WHERE 조건절]

 

■ UPDATE 실습 1

--○ TBL_SAWON 테이블에서 사원번호 1005번 사원의
--   주민번호를 『7609172234567』로 수정한다.
UPDATE TBL_SAWON
SET JUBUN = '7609172234567'
WHERE SANO = 1005;
--==>> 1 행 이(가) 업데이트되었습니다.

-- 확인
SELECT *
FROM TBL_SAWON;
--==>> 
/*
                :
1005	아이유	7609172234567	2015-10-19	1000
                :
*/

-- 실행 후 COMMIT 또는 ROLLBACK 을 반드시 선택적으로 실행
COMMIT;

 

■ UPDATE 실습 2

--○ TBL_SAWON 테이블에서 1005번 사원의 입사일과 급여를
--   각각 2020-04-01, 1200 으로 변경한다.

UPDATE TBL_SAWON
SET HIREDATE = TO_DATE('2020-04-01', 'YYYY-MM-DD')
  , SAL = 1200
WHERE SANO = 1005;
--==>> 1 행 이(가) 업데이트되었습니다.

-- 확인
SELECT *
FROM TBL_SAWON;
--==>>
/*
                :
1005	아이유	7609172234567	2020-04-01	1200
                :
*/


-- 커밋
COMMIT;
--==>> 커밋 완료.

 

■ UPDATE 실습 3

--○ TBL_INSABACKUP 테이블에서
--   과장과 부장만 수당 10% 인상하는 쿼리문을 작성한다.


UPDATE TBL_INSABACKUP
SET SUDANG = SUDANG*1.1
WHERE JIKWI IN ('과장', '부장');

-- 확인
SELECT *
FROM TBL_INSABACKUP;

COMMIT;

 

■ UPDATE 실습 4

--○ TBL_INSABACKUP 테이블에서
--   전화번호가 016, 017, 018, 019로 시작하는 전화번호인 경우
--   이를 모두 010 으로 변경하는 쿼리문을 구성한다.



UPDATE TBL_INSABACKUP
SET TEL = '010' || SUBSTR(TEL,4)
WHERE SUBSTR(TEL,1,3) IN ('016', '017', '018', '019');

SELECT *
FROM TBL_INSABACKUP;

COMMIT;

 

(파일명 20231101_02_hr)

 

■ UPDATE 실습 5

--○ EMPLOYEES 테이블의 직원들 SALARY 를 10% 인상한다.
--   단, 부서명이 'IT'인 직원들만 한정한다.
--   (또한, 쿼리문에 대한 변경 결과 확인 후 ROLLBACK 을 수행한다~!!!!)

-- 내가 풀어본 것
UPDATE EMPLOYEES
SET SALARY = SALARY * 1.1
WHERE DEPARTMENT_ID = (SELECT D.DEPARTMENT_ID
                       FROM EMPLOYEES E, DEPARTMENTS D
                       WHERE E.DEPARTMENT_ID = D.DEPARTMENT_ID
                       AND D.DEPARTMENT_NAME = 'IT'
                       GROUP BY D.DEPARTMENT_ID);

-- IT 부서 직원들의 FIRST_NAME, LAST_NAME, SALARY, DEPARTMENT_ID 조회
SELECT FIRST_NAME, LAST_NAME, SALARY, DEPARTMENT_ID
FROM EMPLOYEES;

SELECT FIRST_NAME, LAST_NAME, SALARY, DEPARTMENT_ID
FROM EMPLOYEES
WHERE 부서명 = 'IT';

SELECT FIRST_NAME, LAST_NAME, SALARY, DEPARTMENT_ID
FROM EMPLOYEES
WHERE 부서아이디 = 부서명이 'IT'인 부서의 부서아이디;

SELECT FIRST_NAME, LAST_NAME, SALARY, DEPARTMENT_ID
FROM EMPLOYEES
WHERE DEPARTMENT_ID = (부서명이 'IT'인 부서의 부서아이디);

SELECT FIRST_NAME, LAST_NAME, SALARY, DEPARTMENT_ID
FROM EMPLOYEES
WHERE DEPARTMENT_ID = (SELECT DEPARTMENT_ID
                       FROM DEPARTMENTS
                       WHERE DEPARTMENT_NAME='IT');
--==>>
/*
Alexander	Hunold	    9000	60
Bruce	    Ernst	    6000	60
David	    Austin	    4800	60
Valli	    Pataballa	4800	60
Diana	    Lorentz	    4200	60
*/


SELECT FIRST_NAME, LAST_NAME, SALARY, DEPARTMENT_ID
     , SALARY * 1.1 "10% 인상된 급여"
FROM EMPLOYEES
WHERE DEPARTMENT_ID = (SELECT DEPARTMENT_ID
                       FROM DEPARTMENTS
                       WHERE DEPARTMENT_NAME='IT');
--==>>
/*
Alexander	Hunold	    9000	60	9900
Bruce	    Ernst	    6000	60	6600
David	    Austin	    4800	60	5280
Valli	    Pataballa	4800	60	5280
Diana	    Lorentz 	4200	60	4620
*/

UPDATE EMPLOYEES
SET SALARY = SALARY * 1.1
WHERE DEPARTMENT_ID = (SELECT DEPARTMENT_ID
                       FROM DEPARTMENTS
                       WHERE DEPARTMENT_NAME='IT');
--==>> 5개 행 이(가) 업데이트되었습니다.

-- 확인
SELECT *
FROM EMPLOYEES;

ROLLBACK;

 

■ UPDATE 실습 6

--○ EMPLOYEES 테이블에서 JOB_TITLE이 'Sales Manager'인 사원들의
--   SALARY 를 해당 직무(직종)의 최고급여(MAX_VALUE)로 수정한다.
--   단, 입사일이 2006년 이전(해당 년도 제외) 입사자에 한해 적용할 수 있도록 처리한다.
--   (또한, 변경에 대한 결과 확인 후 ROLLBACK 수행한다~!!!)

-- 내가 했어용~~~!
UPDATE EMPLOYEES
SET SALARY = (SELECT MAX_SALARY
              FROM JOBS
              WHERE JOB_ID = (SELECT JOB_ID
                              FROM JOBS
                              WHERE JOB_TITLE = 'Sales Manager'))                         
WHERE JOB_ID = (SELECT JOB_ID
                FROM JOBS
                WHERE JOB_TITLE = 'Sales Manager')
  AND HIRE_DATE < TO_DATE('2006', 'YYYY');


-- 같이 했어요~!
UPDATE EMPLOYEES
SET SALARY = (SELECT MAX_SALARY
              FROM JOBS
              WHERE JOB_TITLE = 'Sales Manager')
WHERE JOB_ID = (SELECT JOB_ID
                FROM JOBS
                WHERE JOB_TITLE = 'Sales Manager')
  AND TO_NUMBER(TO_CHAR(HIRE_DATE, 'YYYY')) < 2006;


-- 'Sales Manager'의 MAX_SALSARY
SELECT MAX_SALARY
FROM JOBS
WHERE JOB_TITLE = 'Sales Manager';
--==>> 20080

-- 'Sales Manager'의 JOB_ID
SELECT JOB_ID
FROM JOBS
WHERE JOB_TITLE = 'Sales Manager';


ROLLBACK;
--==>> 롤백 완료.

 

■ UPDATE 실습 7

--○ EMPLOYEES 테이블에서 SALARY 를
--   각 부서의 이름별로 다른 인상률을 적용하여 수정할 수 있도록 한다.
--   Finance → 10% 인상
--   Executive → 15% 인상
--   Accounting → 20% 인상
--   (쿼리문에 의한 변경 결과 확인 후 ROLLBACK 수행~!!!)

-- 내가 풀어보았지용~~
-- CASE WHEN THEN 쓰는데 SELECT 를 안쓸줄은 생각도 못했네요~~~!!!!
UPDATE EMPLOYEES
SET SALARY = CASE DEPARTMENT_ID WHEN (SELECT DEPARTMENT_ID
                                              FROM DEPARTMENTS
                                              WHERE DEPARTMENT_NAME = 'Finance') THEN (SALARY*1.1)
                                        WHEN (SELECT DEPARTMENT_ID
                                              FROM DEPARTMENTS
                                              WHERE DEPARTMENT_NAME = 'Executive') THEN (SALARY*1.15)
                                        WHEN (SELECT DEPARTMENT_ID
                                              FROM DEPARTMENTS
                                              WHERE DEPARTMENT_NAME = 'Accounting') THEN (SALARY*1.2)
                                        ELSE SALARY
                     END
WHERE DEPARTMENT_ID IN (SELECT DEPARTMENT_ID
                        FROM DEPARTMENTS
                        WHERE DEPARTMENT_NAME IN ('Finance', 'Executive', 'Accounting'));

-- DECODE를 써봤어용
UPDATE EMPLOYEES
SET SALARY = DECODE(DEPARTMENT_ID, (SELECT DEPARTMENT_ID
                                    FROM DEPARTMENTS
                                    WHERE DEPARTMENT_NAME = 'Finance'), (SALARY*1.1)
                                 , (SELECT DEPARTMENT_ID
                                    FROM DEPARTMENTS
                                    WHERE DEPARTMENT_NAME = 'Executive'), (SALARY*1.15)
                                 , (SELECT DEPARTMENT_ID
                                    FROM DEPARTMENTS
                                    WHERE DEPARTMENT_NAME = 'Accounting'), (SALARY*1.2))        
WHERE DEPARTMENT_ID IN (SELECT DEPARTMENT_ID
                        FROM DEPARTMENTS
                        WHERE DEPARTMENT_NAME IN ('Finance', 'Executive', 'Accounting'));


-- 같이 풀어볼까요~?

SELECT *
FROM DEPARTMENTS
WHERE DEPARTMENT_NAME IN ('Finance', 'Executive', 'Accounting');


SELECT DEPARTMENT_ID
FROM DEPARTMENTS
WHERE DEPARTMENT_NAME IN ('Finance', 'Executive', 'Accounting');
--==>>
/*
90
100
110
*/

SELECT *
FROM EMPLOYEES
WHERE DEPARTMENT_ID IN (SELECT DEPARTMENT_ID
                        FROM DEPARTMENTS
                        WHERE DEPARTMENT_NAME IN ('Finance', 'Executive', 'Accounting'));


UPDATE EMPLOYEES
SET SALARY = CASE DEPARTMENT WHEN ('Finance'의 부서아이디) 
                             THEN SALARY * 1.1
                             WHEN ('Executive'의 부서아이디)
                             THEN SALARY * 1.15
                             WHEN ('Accounting'의 부서아이디)
                             THEN SALARY * 1.2
                             ELSE SALARY
             END
WHERE DEPARTMENT_ID IN (SELECT DEPARTMENT_ID
                        FROM DEPARTMENTS
                        WHERE DEPARTMENT_NAME IN ('Finance', 'Executive', 'Accounting'));

-- ('Finance'의 부서아이디) 
SELECT DEPARTMENT_ID
FROM DEPARTMENTS
WHERE DEPARTMENT_NAME = 'Finance';
--==>> 100

-- ('Executive'의 부서아이디) 
SELECT DEPARTMENT_ID
FROM DEPARTMENTS
WHERE DEPARTMENT_NAME = 'Executive';
--==>> 90

-- ('Accounting'의 부서아이디) 
SELECT DEPARTMENT_ID
FROM DEPARTMENTS
WHERE DEPARTMENT_NAME = 'Accounting';
--==>> 110


UPDATE EMPLOYEES
SET SALARY = CASE DEPARTMENT WHEN (SELECT DEPARTMENT_ID
                                   FROM DEPARTMENTS
                                   WHERE DEPARTMENT_NAME = 'Finance') 
                             THEN SALARY * 1.1
                             WHEN (SELECT DEPARTMENT_ID
                                   FROM DEPARTMENTS
                                   WHERE DEPARTMENT_NAME = 'Executive')
                             THEN SALARY * 1.15
                             WHEN (SELECT DEPARTMENT_ID
                                   FROM DEPARTMENTS
                                   WHERE DEPARTMENT_NAME = 'Accounting')
                             THEN SALARY * 1.2
                             ELSE SALARY
             END
WHERE DEPARTMENT_ID IN (SELECT DEPARTMENT_ID
                        FROM DEPARTMENTS
                        WHERE DEPARTMENT_NAME IN ('Finance', 'Executive', 'Accounting'));

 

 

■ DELETE

1. 테이블에서 지정된 행(레코드)를 삭제하는데 사용하는 구문

 

2. 형식 및 구조

    DELETE [FROM] 테이블명

    [WHERE 조건절];

    (→ 되도록이면 FROM 은 써주자. SELECT로 데이터 확인 후 DELETE하자!)

 

■ DELETE 실습1 (다른 레코드에 의해 참조당하고 있는 경우 삭제 불가)

--○ EMPLOYEES 테이블에서 직원들의 데이터를 삭제한다.
--   단, 부서명이 'IT'인 경우로 한정한다.

SELECT *
FROM EMPLOYEES
WHERE DEPARTMENT_ID = (SELECT DEPARTMENT_ID
                       FROM DEPARTMENTS
                       WHERE DEPARTMENT_NAME = 'IT');

결과적으로 이 DELETE 쿼리문은 실행되지 않는다.

삭제하려는 데이터가 다른 레코드에 의해 참조당하고 있기 때문!

(ORA-02292: integrity constraint (HR.DEPT_MGR_FK) violated - child record found)