How to form a MySQL Conditional Insert?

MySQLMySQLi Database

For this, you can insert using MySQL dual table. Let us create a table to understand the concept conditional insert. The query to create a table is as follows −

mysql> create table ConditionalInsertDemo
   -> (
   -> UserId int,
   -> TotalUser int,
   -> NumberOfItems int
   -> );
Query OK, 0 rows affected (0.58 sec)

Insert some records in the table using insert command. The query is as follows −

mysql> insert into ConditionalInsertDemo values(101,560,780);
Query OK, 1 row affected (0.19 sec)

mysql> insert into ConditionalInsertDemo values(102,660,890);
Query OK, 1 row affected (0.20 sec)

mysql> insert into ConditionalInsertDemo values(103,450,50);
Query OK, 1 row affected (0.15 sec)

Display all records from the table using select statement. The query is as follows −

mysql> select *from ConditionalInsertDemo;

Output

+--------+-----------+---------------+
| UserId | TotalUser | NumberOfItems |
+--------+-----------+---------------+
|    101 | 560       |           780 |
|    102 | 660       |           890 |
|    103 | 450       |            50 |
+--------+-----------+---------------+
3 rows in set (0.00 sec)

Now you have 3 records in the table. You can insert the next records with conditional insert with the help of dual table. The query inserts records in the table whenever UserId=104 and NumberOfItems=3500 must not be present in the table already. The conditional insert query is as follows −

mysql> insert into ConditionalInsertDemo(UserId,TotalUser,NumberOfItems)
   -> select 104,900,3500 from dual
   -> WHERE NOT EXISTS (SELECT * FROM ConditionalInsertDemo
   -> where UserId=104 and NumberOfItems=3500);
Query OK, 1 row affected (0.18 sec)
Records: 1 Duplicates: 0 Warnings: 0

Now you can check the table, the record is inserted or not. The query to display all records is as follows −

mysql> select *from ConditionalInsertDemo;

Output

+--------+-----------+---------------+
| UserId | TotalUser | NumberOfItems |
+--------+-----------+---------------+
|    101 |       560 |           780 |
|    102 |       660 |           890 |
|    103 |       450 |            50 |
|    104 |       900 |          3500 |
+--------+-----------+---------------+
4 rows in set (0.00 sec)
raja
Published on 10-Jan-2019 11:33:31
Advertisements