Latest Tutorials| Questions and Answers|Ask Questions?|Site Map



Home Sql Mysql-example MySQL Not in Example

Related Tutorials


 
 

Share on Google+Share on Google+

MySQL Not in Example

Advertisement
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)
Advertisement

If you enjoyed this post then why not add us on Google+? Add us to your Circles



Liked it!  Share this Tutorial


Follow us on Twitter, or add us on Facebook or Google Plus to keep you updated with the recent trends of Java and other open source platforms.

Posted on: February 4, 2009

Related Tutorials

Discuss: MySQL Not in Example  

Post your Comment


Your Name (*) :
Your Email :
Subject (*):
Your Comment (*):
  Reload Image
 
 
Comments:0
DMCA.com