MySQL IN Clause


IN Clause is used to validate all the rows from a domain of values that are placed inside ( ). It is used in MySQL Where clause to filter out the records. The general syntax of IN clause is as follows

Syntax

select column1, column2......
where column1 IN ( value1, value2, value2.......)

or 

Update Table Name set ColumnName = newValue
where columnName IN ( value1, value2, value2, value4......)

or 

DELETE from Table Name
where columnName IN ( value1, value2, value2, value4......)

In Clause in Update Command

mysql> select * from student ;
+-------+---------+--------+-------+-------+-------------+--------+
| admno | name    | stream | marks | grade | phone       | gender |
+-------+---------+--------+-------+-------+-------------+--------+
| 101   | rakesh  | comm   | 89.00 | A     | 9871812332  | NULL   |
| 102   | udit    | sci    | 56.50 | B     | 0000000000  | NULL   |
| 103   | mohit   | bio    | 46.50 | C     | 0000000001  | NULL   |
| 104   | mannat  | human  | 67.50 | A     | 99887766551 | NULL   |
| 105   | unnati  | comm   | 77.50 | B     | 96887766551 | NULL   |
| 106   | nikunj  | sci    | 79.50 | A     | 96787766551 | NULL   |
| 107   | pushkar | comm   | 54.20 | C     | 96787466551 | NULL   |
| 108   | pratham | comm   | 84.80 | A     | 9678784651  | NULL   |
| 109   | mohit   | comm   | 64.80 | B     | 9673784651  | NULL   |
| 110   | urshavi | bio    | 96.80 | A     | 92673784651 | NULL   |
+-------+---------+--------+-------+-------+-------------+--------+
10 rows in set (0.00 sec)

mysql> update student set gender ='F' where name IN ('mannat','unnati','urvashi');
Query OK, 2 rows affected (0.00 sec)
Rows matched: 2  Changed: 2  Warnings: 0

Above Command is going to change the column value of ‘mannat’, ‘unnati’ and ‘urvashi’ Only as they are inside the () of IN clause.

using NOT IN operator

mysql> update student set gender ='M' where name NOT IN ('mannat','unnati','urvashi');
Query OK, 8 rows affected (0.00 sec)
Rows matched: 8  Changed: 8  Warnings: 0

mysql> select * from student;
+-------+---------+--------+-------+-------+-------------+--------+
| admno | name    | stream | marks | grade | phone       | gender |
+-------+---------+--------+-------+-------+-------------+--------+
| 101   | rakesh  | comm   | 89.00 | A     | 9871812332  | M      |
| 102   | udit    | sci    | 56.50 | B     | 0000000000  | M      |
| 103   | mohit   | bio    | 46.50 | C     | 0000000001  | M      |
| 104   | mannat  | human  | 67.50 | A     | 99887766551 | F      |
| 105   | unnati  | comm   | 77.50 | B     | 96887766551 | F      |
| 106   | nikunj  | sci    | 79.50 | A     | 96787766551 | M      |
| 107   | pushkar | comm   | 54.20 | C     | 96787466551 | M      |
| 108   | pratham | comm   | 84.80 | A     | 9678784651  | M      |
| 109   | mohit   | comm   | 64.80 | B     | 9673784651  | M      |
| 110   | urshavi | bio    | 96.80 | A     | 92673784651 | M      |
+-------+---------+--------+-------+-------+-------------+--------+
10 rows in set (0.00 sec)
Print Friendly, PDF & Email

Related Posts

If you like CBSEToaday and would like to contribute, you can also write an article using submit article or mail your article to contribute@cbsetoday.com See your article appearing on the cbsetoday.com main page and help other students/teachers.