MySQL Not in Example
MySQL Not in is used to set the update of records for only those id,
which are not specified in the where Not In.
Understand with Example
The Tutorial shows you an example from MySQL Not in. To understand the
example we create a table 'Employee', which has required column names and
datatypes specified.
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 insertion of data in table:
The insert into is used to add the records values into 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:
The Query select return the records from table employee.
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 to use Mysql not in :
The Query below is used to update the employee table and
set keyword is used to set the Empname to 'girish' for only those
records 'Empid' which are not specified in the Where NOT IN clause.
mysql> UPDATE employee SET Empname='Girish' WHERE EmpId NOT IN (2,3,4,5,10);
Query OK, 1 row affected (0.02 sec)
Rows matched: 1 Changed: 1 Warnings: 0
|
Output:-
mysql> select * from employee;
+-------+----------+--------+------------+
| Empid | Empname | Salary | DOB |
+-------+----------+--------+------------+
| 1 | Girish | 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)
|
|
Del.icio.us
Digg
Google
Spurl
Blink
Furl
Simpy
Y! MyWeb
