2021-12-14 18:38 작성

My SQL basic 메모

Table of contents

• MySQL UPDATE 구문 소개
• MySQL UPDATE 예시
  • 1) 하나의 column 안에 있는 values를 수정하는데 MySQL UPDATE 사용하기 예시
  • 2) 복수의 columns에서 values를 수정하는데 MySQL UPDATE 사용하기
  • 3) string을 대체하는데 MySQL UPDATE 사용하기 예시
  • 4) SELECT 구문에 의해 반환되는 rows를 업데이트 하는데 MySQL UPDATE 사용하기 예시

---

MySQL UPDATE 구문 소개

UPDATE 구문은 어떤 table의 데이터를 업데이트 한다. 이 구문은 한 개의 row 혹은 복수의 rows에서 한 개 혹은 다수의 columns 안에 있는 values를 바꿀 수 있게 한다.

다음은 UPDATE 구문의 기본적 문법이다:

UPDATE [LOW_PRIORITY] [IGNORE] table_name
SET
    column_name1 = expr1,
    column_name2 = expr2,
    ...
[WHERE
    condition];

이 문법은:

• 첫째, UPDATE 키워드 다음에 데이터를 업데이트 하고자 하는 특정 table의 명칭을 명시한다.
• 둘째, 어떤 column을 업데이트 하고 싶은지 명시하고 새로운 value를 SET 절에 작성한다. 복수의 columns의 values를 업데이트 하기 위해 문자 value, expression 혹은 subquery 형태의 value를 개별 column에 할당하고 이 때, 콤마로 구분하여 리스트를 만든다.
• 셋째, WEHRE 절에서 조건을 덧붙여 어떤 rows에서 업데이트 되도록 할 것인지 명시한다. WHERE 절은 선택적이다. 만약 제외하면 UPDATE 구문을 특정 table의 모든 rows에 적용한다.

WHERE 절은 매우 중요하므로 잊어서는 안 된다. 뜻하지 않게 WHERE 절을 잊게 되면 특정 table의 모든 rows를 업데이트하게 될 것이다.

MySQL은 UPDATE 구문에서 두 가지 수식어를 지원한다.

1. LOW_PRIORITY 수식어는 UPDATE 구문에 table에서 데이터를 읽는 연결이 없어질 때까지 업데이트를 미루도록 지시한다. LOW_PRIORITY는 MyISAM, MERGE 그리고 MEMORY와 같은 table 수준의 locking을 사용하는 storage engines에서 효과를 발휘한다.
2. IGNORE 수식어는 UPDATE 구문이 에러가 발생해도 업데이트 할 수 있도록 한다. duplicate-key conflicts(중복-키 충돌)와 같은 에러를 발생시키는 rows는 업데이트하지 않는다.

MySQL UPDATE 예시

1) 하나의 column 안에 있는 values를 수정하는데 MySQL UPDATE 사용하기 예시

다음의 employees table을 보자.

이 예시에서 Mary Patterson의 email을 새로운 email인 mary.patterso@classicmodelcars.com으로 업데이트할 것이다.

첫째, employees table에서 SELECT 구문을 사용해 Mary의 email 찾는다.

SELECT
    firstname,
    lastname,
    email
FROM
    employees
WHERE
    employeeNumber = 1056;

output:

firstname	lastname	email
Mary	Patterson	mpatterso@classicmodelcars.com

둘째, Mary의 email 주소를 새로운 email인 mary.patterson@classicmodelcars.com으로 업데이트한다.

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

MySQL은 영향을 받은 rows의 수를 출력한다:

Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

이 UPDATE 구문에서:

• WHERE절에서 명시하는 1056 번호를 가진 employee row가 업데이트 된다.
• SET 절은 email column의 value를 새로운 email로 설정한다.

셋째, 변경된 것을 확인하기 위해 SELECT 구문을 실행한다.

SELECT 
    firstname, 
    lastname, 
    email
FROM
    employees
WHERE
    employeeNumber = 1056;

output:

firstname	lastname	email
Mary	Patterson	mary.patterson@classicmodelcars.com

2) 복수의 columns에서 values를 수정하는데 MySQL UPDATE 사용하기

복수의 columns에서 values를 업데이트 하기 위해 SET 절에서 할당해야 하는 것을 명시할 필요가 있다. 예를 들어, 다음의 구문은 employee number 1056의 last name과 email을 모두 업데이트한다:

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

변경된 점을 확인하자:

SELECT 
    firstname, 
    lastname, 
    email
FROM
    employees
WHERE
    employeeNumber = 1056;

output:

firstname	lastname	email
Mary	Hill	mary.hill@classicmodelcars.com

3) string을 대체하는데 MySQL UPDATE 사용하기 예시

다음의 예시는 office code가 6이며 모두 Sales Reps인 emails의 domain 부분을 업데이트한다:

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

output: SELECT를 이용해 확인하면 다음과 같은 결과가 나온다.

email
afixter@mysqltutorial.org
pmarsh@mysqltutorial.org
tking@mysqltutorial.org

4) SELECT 구문에 의해 반환되는 rows를 업데이트 하는데 MySQL UPDATE 사용하기 예시

다른 tables에서 데이터를 쿼리하는 SELECT 구문에서 반환되는 values를 SET 절에 제공할 수 있다.

예를 들어, customers table에서 몇몇의 customers는 어떤 sale representative도 가지고 있지 않다. 다음과 같이 saleRepEmployeeNumber column의 value는 NULL이다:

SELECT
    customername,
    salesRepEmployeeNumber
FROM
    customers
WHERE
    salesRepEmployeeNumber IS NULL;

sale representative를 가져와 상기의 customers에 업데이트 할 수 있다.

그렇게 하기 위해서 employees table에서 job title이 Sales Rep인 employee를 무작위로 선택해 customers table에 업데이트 할 수 있다.

다음의 쿼리는 job title이 Sales Rep인 employees table에서 employee를 무작위로 선택하는 쿼리다.

SELECT
    employeeNumber
FROM
    employees
WHERE
    jobtitle = 'Sales Rep'
ORDER BY RAND()
LIMIT 1;

customers table에 sales representative employee number column을 업데이트 하기 위해 상기의 쿼리를 UPDATE 구문의 SET 절에 다음과 같이 위치 시킨다:

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

salesRepEmployeeNumber는 모두 채워졌을 것이므로 다음의 구문은 어떤 row도 반환하지 않을 것이다.

SELECT 
     salesRepEmployeeNumber
FROM
    customers
WHERE
    salesRepEmployeeNumber IS NULL;