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)
|
|
Del.icio.us
Digg
Google
Spurl
Blink
Furl
Simpy
Y! MyWeb
