Home  >  Article  >  Database  >  How can we use WHERE clause with MySQL INSERT INTO command?

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

PHPz
PHPzforward
2023-09-05 13:41:08740browse

我们如何将 WHERE 子句与 MySQL INSERT INTO 命令一起使用?

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!

Statement:
This article is reproduced at:tutorialspoint.com. If there is any infringement, please contact admin@php.cn delete