MySQL - UPDATE

Update

기본 형식 : UPDATE <Table Name> SET <list of changes want to make> WHERE <condition>

업데이트 전

mysql> SELECT * FROM cats;
+--------+----------------+------------+------+
| cat_id | name           | breed      | age  |
+--------+----------------+------------+------+
|      1 | Ringo          | Tabby      |    4 |
|      2 | Cindy          | Maine Coon |   10 |
|      3 | Dumbledore     | Maine Coon |   11 |
|      4 | Egg            | Persian    |    4 |
|      5 | Misty          | Tabby      |   13 |
|      6 | George Michael | Ragdoll    |    9 |
|      7 | Jackson        | Sphynx     |    7 |
+--------+----------------+------------+------+
7 rows in set (0.00 sec)

UPDATE

mysql> UPDATE cats SET breed = 'Shorthair'
    -> WHERE breed = 'Tabby';
Query OK, 2 rows affected (0.01 sec)
Rows matched: 2  Changed: 2  Warnings: 0

Entire line is going to fined in cat table all the cats who have a breed of Tabby and change that breed to be short hair.
We have UPDATE table name then SET(And This is a list of the changes we want to make)
WHERE 부분 : Where tell us which items in particular we want to select to update.

mysql> select * from cats;
+--------+----------------+------------+------+
| cat_id | name           | breed      | age  |
+--------+----------------+------------+------+
|      1 | Ringo          | Shorthair  |    4 |
|      2 | Cindy          | Maine Coon |   10 |
|      3 | Dumbledore     | Maine Coon |   11 |
|      4 | Egg            | Persian    |    4 |
|      5 | Misty          | Shorthair  |   13 |
|      6 | George Michael | Ragdoll    |    9 |
|      7 | Jackson        | Sphynx     |    7 |
+--------+----------------+------------+------+
7 rows in set (0.00 sec)

Another one : UPDATE cats SET age = 14 WHERE name = 'Misty';

mysql> UPDATE cats
    -> SET age = 14
    -> WHERE name = 'Misty';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from cats;
+--------+----------------+------------+------+
| cat_id | name           | breed      | age  |
+--------+----------------+------------+------+
|      1 | Ringo          | Shorthair  |    4 |
|      2 | Cindy          | Maine Coon |   10 |
|      3 | Dumbledore     | Maine Coon |   11 |
|      4 | Egg            | Persian    |    4 |
|      5 | Misty          | Shorthair  |   14 |
|      6 | George Michael | Ragdoll    |    9 |
|      7 | Jackson        | Sphynx     |    7 |
+--------+----------------+------------+------+
7 rows in set (0.00 sec)

13이었던 Misty의 age가 14로 변경된 것을 볼 수 있다.

A Good Rule of 👍

There is a good rule of thumb that you should follow. When you are updating something you should make sure that you're targeting the right data before you actually do the update. So basiclly run the Select the equivalent select using your WHEREquery and once you make sure that returns the data you expect then update it just so that you avoid accidentally updating the wrong data.And the same holds true when we're talking about deleting which will do in just a moment but the core idea is that there is no undo button so you could update something back manually if you messed it up. But you always want to just make sure you're targeting what you mean to target before you hit enter on your update.