20201028_23 UPDATE, MERGE

INSERT ALL
    WHEN HIREDATE > '2010/01/01' -- 고용일이 '2010/01/01' 이후인 사원
    THEN INTO emp_hire VALUES(empno, ename, hiredate)
    WHEN sal>=2000 -- 급여가 2000 이상인 사원
    THEN INTO emp_sal VALUES(empno, ename, sal)
SELECT empno, ename, hiredate, sal FROM emp;

emp_hiredate테이블

emp_sal테이블

 

 

 

 

• UPDATE

UPDATE emp
SEt sal = 1200
WHERE empno = 7369;

update할때는 where절 꼭 안해주면 다 업데이트됨

 

 

 

 

 

UPDATE emp
SET sal = sal * 1.1
WHERE deptno IN (SELECT deptno FROM dept WHERE dname IN ('SALES', "ACCOUNTRING'));
--WHERE deptno IN (10,30);값을 알때

 

 

 

 

• DELETE는 위험하니까 조건부터쓰고 DELETE쓰기

DELETE FROM emp
WHERE sal <3000;

 

 

 

 

 

 

• MERGE

MERGE INTO emp_merge_ex m
USING(
    SELECT empno, ename, deptno, sal FROM emp WHERE deptno IN (20, 30)) e
ON (m.empno = e.empno)
WHEN MATCHED THEN
    UPDATE SET m.sal = round(m.sal * 1.1)
WHEN NOT MATCHED THEN
    INSERT (m.empno, m.ename, m.sal, m.deptno) 
    VALUES (e.empno, e.ename, e.sal, e.deptno)
    WHERE e.sal > 1000;

 

 

 

 

---

CREATE TABLE bank_account (
    id NUMBER PRIMARY KEY,
    name VARCHAR2(32) NOT NULL,
    money NUMBER DEFAULT 0
);

SELECT * FROM bank_account;
INSERT INTO bank_account(id, name, money) VALUES(1, 'A', 1000);
INSERT INTO bank_account(id, name, money) VALUES(2, 'B', 0);
COMMIT;

--A의 계좌를 0으로 업데이트
--B의 계좌를 100으로 업데이트

UPDATE bank_account SET money = 0 WHERE name = 'A'; 
SELECT * FROM bank_account;

롤백하면 다시 그 전 트랜잭션으로 

 

 

UPDATE bank_account SET money = 1000 WHERE name = 'B';

완