How to insert column if not exists in Database Table SQL Query

How to insert records into MySql database if record doesn’t exist

Sometimes you have to insert new records in database but you do not want to insert if data is already exist. 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.

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.

Leave a Reply