How to Update Data?

UPDATE SET Command

Update is DML command. IT is most useful and important command that help to update data mean reset the data in database table.

Syntax

UPDATE table_name SET column_name = data;

Example

mysql> SELECT * FROM mark;
+------+------+------+------+-------+
| id   | m1   | m2   | m3   | total |
+------+------+------+------+-------+
|    1 |   25 |   23 |   22 |  NULL |
|    2 |   99 |   33 |   42 |  NULL |
|    3 |   77 |   64 |   52 |  NULL |
|    4 |   97 |   24 |   12 |  NULL |
|    5 |   37 |   24 |   10 |  NULL |
|    6 |   87 |   74 |   80 |  NULL |
+------+------+------+------+-------+
6 rows in set (0.00 sec)

mysql> UPDATE mark SET total = m1 + m2 + m3;
Query OK, 5 rows affected (0.05 sec)
Rows matched: 6  Changed: 5  Warnings: 0

mysql> SELECT * FROM mark;
+------+------+------+------+-------+
| id   | m1   | m2   | m3   | total |
+------+------+------+------+-------+
|    1 |   25 |   23 |   22 |    70 |
|    2 |   99 |   33 |   42 |   174 |
|    3 |   77 |   64 |   52 |   193 |
|    4 |   97 |   24 |   12 |   133 |
|    5 |   37 |   24 |   10 |    71 |
|    6 |   87 |   74 |   80 |   241 |
+------+------+------+------+-------+
6 rows in set (0.00 sec)

mysql>

AND Operator

The and operator is used with where clause . It is execute then both side condition is true else never work.

Syntax

update_query WHERE condition1 AND condition2;

Example

+------+------+------+------+--------+
| id   | m1   | m2   | m3   | total  |
+------+------+------+------+--------+
|    1 |   99 |   93 |   91 | 283.00 |
|    2 |   69 |   89 |   70 | 228.00 |
|    3 |   10 |   39 |   50 |  99.00 |
|    4 |   40 |   59 |   30 | 129.00 |
|    5 |   60 |   39 |   70 | 169.00 |
|    6 |   60 |    9 |   99 | 168.00 |
|    7 |   60 |   45 |   59 | 164.00 |
+------+------+------+------+--------+
7 rows in set (0.00 sec)

mysql> UPDATE mark SET m2 = m2 + 30 WHERE  m1 = 60 AND m2 = 9;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> SELECT * FROM mark;
+------+------+------+------+--------+
| id   | m1   | m2   | m3   | total  |
+------+------+------+------+--------+
|    1 |   99 |   93 |   91 | 283.00 |
|    2 |   69 |   89 |   70 | 228.00 |
|    3 |   10 |   39 |   50 |  99.00 |
|    4 |   40 |   59 |   30 | 129.00 |
|    5 |   60 |   39 |   70 | 169.00 |
|    6 |   60 |   39 |   99 | 168.00 |
|    7 |   60 |   45 |   59 | 164.00 |
+------+------+------+------+--------+
7 rows in set (0.00 sec)

mysql> UPDATE mark SET m2 = m2 + 30 WHERE  m1 = 60 AND m2 = 9;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 0  Changed: 0  Warnings: 0

mysql>

OR Operator

The or operator is used with where clause . It is execute then if any one side condition is true else never work.

Syntax

update_query WHERE condition1 OR condition2;

Example

mysql> UPDATE mark SET m2 = m2 + 30 WHERE  m1 = 60 OR m2 = 9;
Query OK, 3 rows affected (0.03 sec)
Rows matched: 3  Changed: 3  Warnings: 0

mysql> SELECT * FROM mark;
+------+------+------+------+--------+
| id   | m1   | m2   | m3   | total  |
+------+------+------+------+--------+
|    1 |   99 |   93 |   91 | 283.00 |
|    2 |   69 |   89 |   70 | 228.00 |
|    3 |   10 |   39 |   50 |  99.00 |
|    4 |   40 |   59 |   30 | 129.00 |
|    5 |   60 |   69 |   70 | 169.00 |
|    6 |   60 |   69 |   99 | 168.00 |
|    7 |   60 |   75 |   59 | 164.00 |
+------+------+------+------+--------+
7 rows in set (0.00 sec)

mysql>

BETWEEN Operator

The between operator is used with and operator . it is use to find bweteen value.

Syntax

update_query WHERE BEWTEEN condition1 AND condition2;

Example

mysql> SELECT * FROM student;
+----+----------+------------+
| id | fname    | lname      |
+----+----------+------------+
|  1 | aman     | kumna      |
|  2 | geeta    | sharma     |
|  3 | sheena   | ryder      |
|  4 | scarlit  | scandal    |
|  5 | richelle | ryan       |
|  6 | monika   | gastamride |
+----+----------+------------+
6 rows in set (0.00 sec)

mysql> UPDATE student SET lname = NULL WHERE id BETWEEN 3 AND 6
	-> ;
Query OK, 4 rows affected (0.04 sec)
Rows matched: 4  Changed: 4  Warnings: 0

mysql> SELECT * FROM student;
+----+----------+--------+
| id | fname    | lname  |
+----+----------+--------+
|  1 | aman     | kumna  |
|  2 | geeta    | sharma |
|  3 | sheena   | NULL   |
|  4 | scarlit  | NULL   |
|  5 | richelle | NULL   |
|  6 | monika   | NULL   |
+----+----------+--------+
6 rows in set (0.00 sec)

mysql>

---

Addition Operator

Syntax

UPDATE table_name SET column_name = data WHERE data1 + data1;

Example

mysql> UPDATE mark SET m3 = 70 WHERE id = 1;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> UPDATE mark SET total = m1 + m2 + m
Query OK, 1 row affected (0.01 sec)
Rows matched: 6  Changed: 1  Warnings: 0

mysql> SELECT * FROM mark;
+------+------+------+------+-------+
| id   | m1   | m2   | m3   | total |
+------+------+------+------+-------+
|    1 |   25 |   23 |   70 |   118 |
|    2 |   99 |   33 |   42 |   174 |
|    3 |   77 |   64 |   52 |   193 |
|    4 |   97 |   24 |   12 |   133 |
|    5 |   37 |   24 |   10 |    71 |
|    6 |   87 |   74 |   80 |   241 |
+------+------+------+------+-------+
6 rows in set (0.00 sec)

mysql>

---

GREATER THEN Operator

NOT is use to conver result in negetive mean if true so false and false so true.

Syntax

update_query [ WHERE column > value ];

Example

mysql> UPDATE mark SET result = 'PASS' WHERE not percentage > 40;
Query OK, 2 rows affected (0.01 sec)
Rows matched: 2  Changed: 2  Warnings: 0

mysql> SELECT * FROM mark;
+------+------+------+------+-------+------------+--------+
| id   | m1   | m2   | m3   | total | percentage | result |
+------+------+------+------+-------+------------+--------+
|    1 |   25 |   23 |   70 |   118 |      39.33 | PASS   |
|    2 |   99 |   33 |   42 |   174 |      58.00 | PASS   |
|    3 |   77 |   64 |   52 |   193 |      64.33 | PASS   |
|    4 |   97 |   24 |   12 |   133 |      44.33 | PASS   |
|    5 |   37 |   24 |   10 |    71 |      23.67 | PASS   |
|    6 |   87 |   74 |   80 |   241 |      80.33 | PASS   |
+------+------+------+------+-------+------------+--------+
6 rows in set (0.00 sec)

mysql>