생활코딩( Database2 MySQL ) (7) - CRUD of Update

1. Update

다음 기본 syntax 사용

UPDATE [LOW_PRIORITY] [IGNORE] table_name
    SET assignment list
    [WHERE where_condition]
    [ORDER BY ...]
    [LIMIT row_count]

assignment : col_name = value

assignment_list : assignment [, assignment ] ...

 

 

1-1) SQL script

-- select opentutorials schema.
USE opentutorials;

-- describe topic.
DESC topic;

-- select from topic
SELECT * FROM topic;

-- update topic I want
UPDATE topic SET description='new MySQL is...', title='the MySQL'  \
        WHERE title='MySQL';

-- select from topic to see the change effect
SELECT * FROM topic;

1) SET을 통해 변경할 값들을 , 로 연결하여 column name = value 의 형식으로 전달

2) 이후에 WHERE 문으로 위의 변경사항을 적용시킬 row data를 선택

> WHERE 문을 적용하지 않으면 모든 row에 변경을 반영하므로 반드시 주의!

 

1-2) output before

+----+---------------+---------------------+---------------------+---------+-------------+
| id | title         | description         | created             | author  | profile     |
+----+---------------+---------------------+---------------------+---------+-------------+
|  1 | MySQL         | MySQL is...         | 2021-05-31 22:31:50 | HJ Yang | developer   |
|  2 | MySQL         | MySQL is...         | 2021-05-31 22:34:53 | HJ Yang | developer   |
|  3 | MySQL         | MySQL is...         | 2021-05-31 22:35:47 | HJ Yang | developer   |
|  4 | MySQL         | MySQL is...         | 2021-05-31 23:06:02 | HJ Yang | developer   |
|  5 | SQL Server    | SQL Server is...    | 2021-05-31 23:06:02 | duruwa  | data admin  |
|  6 | Egoin         | Egoing is...        | 2021-06-01 23:48:46 | Unknown | creator     |
|  7 | Mongus Server | Mongus Server is... | 2021-06-01 23:48:46 | Admin   | is admin... |
+----+---------------+---------------------+---------------------+---------+-------------+
7 rows in set (0.00 sec)

 

1-3) output after

+----+---------------+---------------------+---------------------+---------+-------------+
| id | title         | description         | created             | author  | profile     |
+----+---------------+---------------------+---------------------+---------+-------------+
|  1 | the MySQL     | new MySQL is...     | 2021-05-31 22:31:50 | HJ Yang | developer   |
|  2 | the MySQL     | new MySQL is...     | 2021-05-31 22:34:53 | HJ Yang | developer   |
|  3 | the MySQL     | new MySQL is...     | 2021-05-31 22:35:47 | HJ Yang | developer   |
|  4 | the MySQL     | new MySQL is...     | 2021-05-31 23:06:02 | HJ Yang | developer   |
|  5 | SQL Server    | SQL Server is...    | 2021-05-31 23:06:02 | duruwa  | data admin  |
|  6 | Egoin         | Egoing is...        | 2021-06-01 23:48:46 | Unknown | creator     |
|  7 | Mongus Server | Mongus Server is... | 2021-06-01 23:48:46 | Admin   | is admin... |
+----+---------------+---------------------+---------------------+---------+-------------+
7 rows in set (0.00 sec)

---

참조

https://opentutorials.org/course/3161/19541

SQL의 UPDATE 구문 - 생활코딩