How can we use WHERE clause with MySQL INSERT INTO command?

In the case of inserting new rows, we can use conditional insertion, that is, WHERE clause and INSERT INTO command. It can be done in the following way -

With the help of virtual table

In this case we insert the values ​​from the virtual table along with some status. The syntax is as follows -

INSERT INTO table_name(column1,column2,column3,…) Select value1,value2,value3,… From dual WHERE [conditional predicate];

Example

mysql> Create table testing(id int, item_name varchar(10));
Query OK, 0 rows affected (0.15 sec)

mysql> Insert into testing (id,item_name)Select 1,'Book' From Dual Where 1=1;
Query OK, 1 row affected (0.11 sec)
Records: 1 Duplicates: 0 Warnings: 0

mysql> Select * from testing;

+------+-----------+
| id   | item_name |
+------+-----------+
| 1    | Book      |
+------+-----------+

1 row in set (0.00 sec)

In the above example, we have created a table "testing" and in order to insert rows into it, we are using virtual table dual with conditions. If the condition is true, MySQL will insert the row into the table, otherwise it will not.

With the help of a table with the same structure

If we want to insert into a table with the same structure as another table, then in the following example it is demonstrated how to do conditional insertion i.e. how to use WHERE clause with Used with the INSERT INTO statement.

mysql> Insert into dummy1(id,name)select id, name from dummy Where id =1;
Query OK, 1 row affected (0.06 sec)
Records: 1 Duplicates: 0 Warnings: 0

mysql> select * from dummy;

+------+--------+
| id   | Name   |
+------+--------+
| 1    | Gaurav |
| 2    | Aarav |
+------+--------+

2 rows in set (0.00 sec)

mysql> select * from dummy1;

+------+--------+
| id   | Name   |
+------+--------+
| 1    | Gaurav |
+------+--------+

1 row in set (0.00 sec)

In the above example, we have inserted values ​​in table "dummy1" with the same structure as table "dummy" with the condition that only rows with "id = 1" are inserted.

The above is the detailed content of How can we use WHERE clause with MySQL INSERT INTO command?. For more information, please follow other related articles on the PHP Chinese website!