How to insert records into MySql database if record doesn’t exist
Insert where not exists. Sometimes you have to insert new records in database but you do not want to insert if not exists. In different applications we have to do this. For example if you want to insert some data against date then you have to check if data against that date is already exist in the table or not then you will insert else ignore it. Yes its possible and very simple. In the given query I have inserted some records into power table. In this query I have selected table name, id and then I have used a query where not exists which checks table before inserting.
By using this query you will be able to insert data without any error. It will only update table if there are no records in the selected date.
SQL Insert if Not Exists Query
Here is the query to insert where not exists. You can use it in php mysql check if record exists before insert.
insert into power (power_id, ConsumerID, Date, Total_Power, TimeSpanID,Monthly_Power) SELECT * FROM (SELECT 4, 23, DATE(“2018-04-20”),20,12,200) AS tmp WHERE NOT EXISTS ( SELECT Date FROM power WHERE Date = “2018-04-20″ AND power_id=”4” ) LIMIT 1′
Explaination of the SQL Query and Where Not Exists
In above query data is inserting into power table. Columns are: power_id, ConsumerID, Date, Total_Power, TimeSpanID and Monthly_Power. Then checking date and power_id if records are already exists on the given date and power id then record will not insert else new row will be inserted. WHERE NOT EXISTS ( SELECT Date FROM power WHERE Date = “2018-04-20″ AND power_id=”4”
By using this query you will be able to update data if not exist in MySql database. You can use this query with PHP, java , Python or Node.js. I have tested this sql query in mysql database with PHP Laravel framework and it is working perfectly. You can check with other databases like Postgre SQL and Oracle. Comment below if you want to ask any questions I will answer.