8.1.2 UPDATE 예제

UPDATE 예제는 다음과 같습니다.

-- https://www.mysqltutorial.org/mysql-update-data.aspx
SELECT employeeNumber, lastName, firstName, extension, email, officeCode, reportsTo, jobTitle
FROM history_hub.employees;

-- case 0
UPDATE employees
SET
email = 'mary.patterson@classicmodelcars.com'
WHERE
employeeNumber = 1056;

-- case 1
UPDATE employees
SET
lastname = 'Hill',
email = 'mary.hill@classicmodelcars.com'
WHERE
employeeNumber = 1056;

-- case 3
UPDATE employees
SET email = REPLACE(email,'@classicmodelcars.com','@mysqltutorial.org')
WHERE
jobTitle = 'Sales Rep' AND
officeCode = 6
limit 1;

SELECT employeeNumber, lastName, firstName, extension, email, officeCode, reportsTo, jobTitle
FROM history_hub.employees
WHERE
jobTitle = 'Sales Rep' AND
officeCode = 6
limit 2;

-- case 4
UPDATE customers
SET
salesRepEmployeeNumber = (SELECT
employeeNumber
FROM
employees
WHERE
jobtitle = 'Sales Rep'
ORDER BY RAND()
LIMIT 1)
WHERE
salesRepEmployeeNumber IS NULL
limit 2;

--
CREATE TABLE merits (
performance INT(11) NOT NULL,
percentage FLOAT NOT NULL,
PRIMARY KEY (performance)
);

CREATE TABLE employees2 (
emp_id INT(11) NOT NULL AUTO_INCREMENT,
emp_name VARCHAR(255) NOT NULL,
performance INT(11) DEFAULT NULL,
salary FLOAT DEFAULT NULL,
PRIMARY KEY (emp_id),
CONSTRAINT fk_performance FOREIGN KEY (performance)
REFERENCES merits (performance)
);
-- insert data for merits table
INSERT INTO merits(performance,percentage)
VALUES(1,0),
(2,0.01),
(3,0.03),
(4,0.05),
(5,0.08);
-- insert data for employees table
INSERT INTO employees2(emp_name,performance,salary)
VALUES('Mary Doe', 1, 50000),
('Cindy Smith', 3, 65000),
('Sue Greenspan', 4, 75000),
('Grace Dell', 5, 125000),
('Nancy Johnson', 3, 85000),
('John Doe', 2, 45000),
('Lily Bush', 3, 55000);

-- case5 (실행안됨.)
UPDATE employees2
INNER JOIN
merits ON employees2.performance = merits.performance
SET
salary = salary + salary * percentage
where emp_id < 10;

-- case 6(실행 안됨)
UPDATE employees2
LEFT JOIN
merits ON employees2.performance = merits.performance
SET
salary = salary + salary * 0.015
WHERE
merits.percentage IS NULL;