Home Sql Mysql-example MySQL Not in Example

MySQL Not in Example
Posted on: February 4, 2009 at 12:00 AM

MySQL Not in is used to set the update of records for only those id, which are not specified in the where Not In.

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)

Related Tags for MySQL Not in Example:
sql, mysql, c, date, update, sed, id, set, if, for, ie, record, where, records, to, ci, e, not, use, pe, in, no, pda, m, os, os, spec, which, s, sp, at, my, is, ar, rds, upd, s, s, rd, th, only, on, o, nl

«Previous Index Next»