SQL Update

SQL Update is used to modify the existing structure of table. The modification includes setting a new column records values on the basis of condition specified in Where Clause.

Understand with Example

The Tutorial illustrate an example from 'SQL Update'. To understand the example we create a table 'Employee' with the required field name and datatype respectively.

Query for creating table name Employee:

mysql> CREATE TABLE Employee(
    ->              Empid int(10),
    ->              Empname varchar(60),
    ->              Salary int(90),
    ->       DOB date
    ->            );
Query OK, 0 rows affected (0.01 sec)

Query for Multiple insertion of data in table:

The insert into keyword is used to add the values to the table 'Employee'.

mysql>     INSERT INTO Employee (Empid,Empname,Salary,DOB) VALUES
    ->     (01,'Habib',2014,20041202),
    ->     (02,'Karan',4021,20030411),
    ->     (03,'Samia', 22,20080223),
    ->     (04,'Hui Ling', 25,20081015),
    ->     (05,'Yumie', 29,19990126);
Query OK, 5 rows affected (0.00 sec)
Records: 5  Duplicates: 0  Warnings: 0

Query to view data inserted in table:

mysql> select * from employee;

Output:-

+-------+----------+--------+------------+
| Empid | Empname  | Salary | DOB        |
+-------+----------+--------+------------+
| 1     | Habib    | 2014   | 2004-12-02 |
| 2     | Karan    | 4021   | 2003-04-11 |
| 3     | Samia    | 22     | 2008-02-23 |
| 4     | Hui Ling | 25     | 2008-10-15 |
| 5     | Yumie    | 29     | 1999-01-26 |
+-------+----------+--------+------------+
5 rows in set (0.00 sec)

Query for multiple updating data inserted in table:

The below Query is used to update the table 'employee' by setting a new values for each employee name based on the condition specified in Where Clause.

mysql> update employee set empname='A'where empid=1;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1  Changed: 0  Warnings: 0

mysql>  update employee set empname='b'where empid=2;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql>  update employee set empname='c'where empid=3;
Query OK, 1 row affected (0.02 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql>  update employee set empname='d'where empid=4;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql>  update employee set empname='e'where empid=5;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

Output:-

mysql> select * from employee;
+-------+---------+--------+------------+
| Empid | Empname | Salary | DOB        |
+-------+---------+--------+------------+
| 1     | A       | 2014   | 2004-12-02 |
| 2     | b       | 4021   | 2003-04-11 |
| 3     | c       | 22     | 2008-02-23 |
| 4     | d       | 25     | 2008-10-15 |
| 5     | e       | 29     | 1999-01-26 |
+-------+---------+--------+------------+
5 rows in set (0.00 sec)

SQL Update

SQL Update

SQL Update

Tutorials