7. 데이터 수정_UPDATE - 패스트캠퍼스 백엔드 부트캠프 3기

1. 데이터 수정 - UPDATE

UPDATE 테이블 SET 열=값, 열=값 [WHERE 조건식];

• WHERE를 이용해 특정 행을 식별(조건에 일치하는 모든 행을 대상으로 업데이트)
• WHERE가 생략된 경우 모든 행이 갱신
• SET에 사용된 =는 대입 연산
• 예제

CREATE DATABASE example;
USE example;
SHOW DATABASES;

CREATE TABLE categories(
    id INT NOT NULL AUTO_INCREMENT,
    name VARCHAR(255) NOT NULL,
    PRIMARY KEY(id)
);

CREATE TABLE products(
    id INT NOT NULL AUTO_INCREMENT,
    name VARCHAR(255) NOT NULL,
    category_id INT NOT NULL,
    price INT NOT NULL,
    registration_date TIMESTAMP NOT NULL,
    PRIMARY KEY (id),
    FOREIGN KEY (category_id) REFERENCES categories (id)
);

SHOW TABLES;
DESC categories;
DESC products;

INSERT INTO categories (name) VALUES
    ('Category 1'),
    ('Category 2'),
    ('Category 3'),
    ('Category 4'),
    ('Category 5'),
    ('Category 6'),
    ('Category 7'),
    ('Category 8'),
    ('Category 9'),
    ('Category 10');
    
SELECT * FROM categories;
    
INSERT INTO products (name, category_id, price, registration_date) VALUES
    ('apple', 1, 1000, '2023-09-20 10:00:00'),
    ('banana', 2, 800, '2023-09-20 11:30:00'),
    ('keyboard', 3, 1200, '2023-09-20 12:45:00'),
    ('monitor', 3, 1500, '2023-09-20 14:20:00'),
    ('small monitor', 2, 750, '2023-09-20 15:10:00'),
    ('big monitor', 4, 2000, '2023-09-20 16:40:00'),
    ('headphone', 3, 1350, '2023-09-20 18:00:00'),
    ('pizza', 1, 950, '2023-09-20 19:30:00'),
    ('chicken', 1, 1800, '2023-09-20 21:15:00'),
    ('glasses', 2, 1000, '2023-09-20 22:45:00');

SELECT * FROM products;
UPDATE products SET price = 1000 WHERE name = 'monitor';
UPDATE products SET registration_date = '2023-09-21 08:00:00' WHERE name = 'monitor';
UPDATE products SET price = price * 0.9 WHERE price >= 1000;

2. 참조 시 UPDATE(on update)

• CASCADE : 참조 데이터 업데이트 시 상대방 데이터도 함께 업데이트
• SET NULL : 참조 데이터 업데이트 시 상대방 테이블의 참조 컬럼을 NULL로 업데이트
• SET DEFAULT : 참조 데이터 업데이트 시 상대방 테이블의 참조 컬럼을 DEFALUT 값으로 업데이트
• RESTRICT : 참조하고 있을 경우, 업데이트 불가
• NO ACTION : RESTRICT와 동일, 옵션을 지정하지 않았을 경우 자동으로 선택
• 예제

CREATE TABLE customers (
    customer_id INT PRIMARY KEY,
    customer_name VARCHAR(255) NOT NULL
);

INSERT INTO customers (customer_id, customer_name) VALUES
    (1, 'usr1'),
    (2, 'usr2'),
    (3, 'usr3');

SELECT * FROM customers;

CREATE TABLE orders(
    order_id INT PRIMARY KEY,
    order_customer_id INT,
    order_total DECIMAL(10, 2),
    CONSTRAINT fk_order_customer FOREIGN KEY( order_customer_id) REFERENCES customers(customer_id)
		ON UPDATE CASCADE
);

INSERT INTO orders (order_id, order_customer_id, order_total) VALUES
    (101, 1, 100.00),
    (102, 2, 50.00),
    (103, 3, 70.00);

SELECT * FROM orders;
SELECT * FROM customers;

UPDATE customers SET customer_id = 4 WHERE customer_id = 1;

ALTER TABLE orders DROP FOREIGN KEY fk_order_customer;

ALTER TABLE orders
    ADD CONSTRAINT fk_order_customer
    FOREIGN KEY (order_customer_id) REFERENCES customers(customer_id)
    ON UPDATE SET NULL;

SELECT * FROM customers;
SELECT * FROM orders;

UPDATE customers SET customer_id = 10 WHERE customer_id = 2;

ALTER TABLE orders DROP FOREIGN KEY fk_order_customer;

ALTER TABLE orders
    ADD CONSTRAINT fk_order_customer
    FOREIGN KEY (order_customer_id) REFERENCES customers(customer_id)
    ON UPDATE RESTRICT;

UPDATE customers SET customer_id = 40 WHERE customer_id = 3;