?
Solved

How do I insert a record into a MySQL Database only if an existing record doesn't exist?

Posted on 2009-12-21
17
Medium Priority
?
364 Views
Last Modified: 2012-06-27
I have MySQL table like the following :

Domain -=- A domain name
Date -=- The date the domain was added to the database
Rank -=- A metric that we calculate for the domain

None of the values are unique. For example you could have the domain name "example.com" with a different Rank value for the past 7 days so there'd be 7 entries in the database all with different dates.

We are calculating a new "Rank" value in PHP but we only want to add it to the database if it's different to the last value in the database. So we need to look at the date column for the specified domain and only add a record if the Rank is different from the currently calculated Rank.

I'm not sure if I should be doing this with multiple queries or if there's a way to do this with one mySQL query?
0
Comment
Question by:TotallyMe
  • 7
  • 6
  • 2
  • +1
17 Comments
 
LVL 6

Expert Comment

by:birwin
ID: 26102694
I couldn't test this, but the basic form of the query should be:
$query = "INSERT INTO database_name (domain, date, rank) values ($domain, NOW(), $rank)  WHERE NOT EXISTS (SELECT * FROM database_name WHERE domain= $domain AND rank=$rank);

 
0
 
LVL 6

Expert Comment

by:birwin
ID: 26102804
I re-read your query, and realized I didn't handle the different from the last entry.
Just add to the select query  
ORDER BY date desc LIMIT 1
 
0
 
LVL 34

Expert Comment

by:Beverley Portlock
ID: 26103155
Make a composite index on the table so that the domain and rank are UNIQUE and then just use an ordinary INSERT statement. The INSERT will fail if the record is already present but you can either catch the failure or just ignore it.

 ALTER TABLE `myTableName` ADD UNIQUE (
`domain` ,
`rank`
)
0
Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

 
LVL 34

Expert Comment

by:Beverley Portlock
ID: 26103172
If you want to catch the error then

mysql_query("INSERT INTO.....

if ( mysql_errno() == 1062 ) {
    .... attempt to add a duplicate failed
}

The advantage of doing it with a UNIQUE index is that the database ensures the uniqueness
0
 

Author Comment

by:TotallyMe
ID: 26103289
birwin: Thanks for your help I'm getting closer but still getting an error when I test this query in phpMyAdmin:

INSERT INTO `my_table` VALUES ('domain.com', now(), '9') WHERE NOT EXIST (SELECT * FROM `my_table` WHERE `domain` = 'domain.com' AND `rank` = 6 ORDER BY `date` DESC LIMIT 1);

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE NOT EXIST (SELECT * FROM `my_table` WHERE `domain` = 'domain.com' AND `rank` ' at line 1
0
 

Author Comment

by:TotallyMe
ID: 26103471
Ok just found one bug, I was using :
"WHERE NOT EXIST"
rather than
"WHERE NOT EXISTS"

Fixed it but I'm still getting same error
0
 

Author Comment

by:TotallyMe
ID: 26103597
I just had a Google and can't seem to find anyone using INSERT statements with WHERE NOT EXISTS so I'm starting to wonder if it's even possible.

I'm trying to look for another way to achieve this. Unfortunately bportlocks method won't work as it doesn't allow me to compare the Rank I'm creating with the current Rank.
0
 
LVL 2

Expert Comment

by:mabbj747
ID: 26103617
Try this

INSERT INTO my_table(domain, date, rank)
SELECT
   vwData.domain
   , vwData.date
   , vwData.rank
FROM
   (
      SELECT
             'domain.com' As domain
            , now() date
            , '9' rank
    )vwData
LEFT OUTER JOIN
   my_table
ON
   vwData.domain = my_table.domain
AND        
   vwData.date = my_table.date
AND        
   vwData.rank = my_table.rank
WHERE
   my_table.domain IS NULL
   
0
 

Author Comment

by:TotallyMe
ID: 26103629
mabbj747: WOW, that blew my mind, didn't realise it could be that confusing. The error it returned was:

#1267 - Illegal mix of collations (utf8_unicode_ci,COERCIBLE) and (latin1_swedish_ci,IMPLICIT) for operation '='
0
 
LVL 6

Accepted Solution

by:
birwin earned 2000 total points
ID: 26104024
As I said, I hadn't tested it. When I did I found you have to slightly hot-wire it, as shown below. In your code you had missed the insert list.  I tested this, and it works perfectly, although, of course, I was using my own database.
Note that the LIMIT 1 is outside the last parenthesis.
$query = "INSERT INTO database_name (domain, date, rank) select '$domain', NOW(), '$rank' FROM database_name  WHERE NOT EXISTS (SELECT * FROM database_name WHERE domain= '$domain' AND rank='$rank' ORDER BY date desc ) LIMIT 1 "
0
 

Author Comment

by:TotallyMe
ID: 26104473
Awesome, that works great. The only thing I've noticed is it doesn't insert anything if the table is empty, as soon as I add one row manually it works fine but it won't insert any data into an empty table.

This is my latest SQL, had to include "AND date != now()"  :

  $sql = "INSERT INTO `domains` (domain, date, rank)
              SELECT '$data[1]', NOW(), '$data[0]' FROM `domains`
              WHERE NOT EXISTS (
                SELECT * FROM `domains` WHERE domain = '$data[1]'
                AND rank = '$data[0]' AND date != now() ORDER BY date DESC
              ) LIMIT 1"
0
 
LVL 6

Expert Comment

by:birwin
ID: 26107231
I don't see how adding the logic of adding AND != date works. Does that allow you to enter into a blank database and it won't without it?
Try adding LIMIT 1 on both sides of the parentheses.
ORDER BY date DESC LIMIT 1 ) LIMIT 1"
I tried that on an empty table and it worked.
0
 

Author Comment

by:TotallyMe
ID: 26109261
The "AND date != now()" is just so it wouldn't add a duplicate entry for the same day.

That extra LIMIT 1 didn't work, I think the problem is because the INSERT is dependent on the SELECT? I've been having a play around with it but can't seem to work it out.

0
 
LVL 6

Expert Comment

by:birwin
ID: 26109342
Can you post the actual structure of your database? Are there any fields other than the three you mentioned? Is your date expressed at datetime or just YYYY-MM-DD?
I tested my code on a database with about a dozen fields. I copied  the database, emptied it and ran the query. It worked as expected without error.
0
 

Author Comment

by:TotallyMe
ID: 26110236
This is the table, yes date is as YYYY-MM-DD.

CREATE TABLE IF NOT EXISTS `domains` (
  `domain` varchar(253) NOT NULL,
  `date` date NOT NULL,
  `rank` int(9) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
0
 
LVL 2

Expert Comment

by:mabbj747
ID: 26110943
TotallyMe, I don't know why you found the query so confusing. It only inserting the data which is not in the tbale by joining to the same table. If you are getting the collation conflicts then you can specify the collation with the string columns in the join class.
0
 
LVL 6

Expert Comment

by:birwin
ID: 26111127
Can you clarify the problem? You earlier mentioned an issue with empty tables. This is a problem that I tested for, but could not duplicate.
I also still don't get why you need the date in the select statement. The point of this code is that if the domain name exists, and it has a ranking equal to the ranking you are planning to insert, then the query should insert nothing.
If it is used on a blank table, it iserts the data, but once it iserts that data, it should be unable to reinsert the same domain / rank information again. if the oldest entry for that domain in the table has the same rank as you are trying to enter. This code does not just test for yesterday's date, it finds the latest entry in the database for that domain, regardless of its date.
I am not sure if your issue is not being able to enter into a blank table, or if the code is throwing off an error. As I mentioned, I have tried several scenarios with a test database, and I cannot duplicate a problem.
0

Featured Post

Free recovery tool for Microsoft Active Directory

Veeam Explorer for Microsoft Active Directory provides fast and reliable object-level recovery for Active Directory from a single-pass, agentless backup or storage snapshot — without the need to restore an entire virtual machine or use third-party tools.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This post contains step-by-step instructions for setting up alerting in Percona Monitoring and Management (PMM) using Grafana.
This holiday season, we’re giving away the gift of knowledge—tech knowledge, that is. Keep reading to see what hacks, tips, and trends we have wrapped and waiting for you under the tree.
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
Suggested Courses
Course of the Month13 days, 23 hours left to enroll

807 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question