[RESOLVED] duplicate records insert issue

[dmacman1962]

Hi All,

I had a series of queries to look up some records and then enter them into a table and mark the records. ( I am saving multiple rows for their email address and whether or not it was a valid address and what email campaign was used. )

My friend is a SQL programmer and looked at all my queries and reduced it to one query. But when I run it, it inserts the records, and then re-inserts them causing duplicates.

But before you suggest I make that a unique field, I need to be able to insert the records later, for different email campaigns). So that would not help me.

Here is the query he wrote.

$sql = "INSERT INTO kickback ";
$sql .= "(maxID, campaignID, kickbacktypeID, kbDate)";
$sql .= " SELECT teachers.maxID, tempaddresses.campaignID, tempaddresses.kickbacktypeID, NOW()
FROM teachers, tempaddresses";
$sql .= " WHERE teachers.Emailaddress = tempaddresses.Emailaddress";

And as usual, I appreciate the help,
Don

[devinemke]

your query might insert multiple rows if there are multiple rows matching your WHERE clause. you could use a LIMIT clause to only insert a single record.

[dmacman1962]

Good thought, but I ran the SELECT by itself:

SELECT teachers.maxID, tempaddresses.campaignID, tempaddresses.kickbacktypeID, NOW()
FROM teachers, tempaddresses
WHERE teachers.Emailaddress = tempaddresses.Emailaddress

And it returns 3 rows from this query, and the tempaddresses has 3 records in it.

But when I run the entire query, my kickback table ends up with 6 records (records 1,2,3 then the same as 4,5,6 - in the same order as well).

I checked the teachers table, and it only has one instance of each of the 3 records as well.

---

if it may somehow effect this, here are the 2 queries that follow this one.

$sql = "INSERT INTO campaign_event (maxID, campaignID, ceDate)";
$sql .= " SELECT teachers.maxID, tempaddresses.campaignID, NOW()
FROM teachers, tempaddresses";
$sql .= " WHERE teachers.Emailaddress = tempaddresses.Emailaddress";

and...

$sql = "UPDATE teachers JOIN tempaddresses";
$sql .= " ON teachers.Emailaddress = tempaddresses.Emailaddress";
$sql .= " SET teachers.Modified = NOW()";

Thoughts?

Thanks,
Don

[laserlight]

Mind giving the relevant test data, actual results, and expected results?

[dmacman1962]

Not at all.

Here are the record values table (the ones used). (FYI we send emails to teachers who signed up for our new training videos and their addresses sometimes change (kickback) or the emails get marked as SPAM). These are subscribed teachers and we are not a spammer, for the record (no pun intended).

(teachers table)
maxID --- emailAddress
1709716 - [e-mail]
1713771 - [e-mail]
1760223 - [e-mail]

(campaign table)
campaignID --- CampaignName
1 ------------------- Drew Carey
2 ------------------- Drew Carey 2

(kickbackType table)
kickbacktypeID ------ Name
1 ----------------------- Kickback
2 ----------------------- Spam

(tempaddresses table)
tempID - EmailAddress - campaignID - kickbacktypeID
1 ---------- [e-mail] ---------- 2 ---------------- 2
2 ---------- [e-mail] ---------- 2 ---------------- 2
3 ---------- [e-mail] ---------- 2 ---------------- 2

(kickback table)
kickbackID ------ maxID ------- campaignID --- kickbacktypeID
1 ----------------- 1760223 --------- 2 ----------------- 2
2 ----------------- 1709716 --------- 2 ----------------- 2
3 ----------------- 1713771 --------- 2 ----------------- 2
4 ----------------- 1760223 --------- 2 ----------------- 2
5 ----------------- 1709716 --------- 2 ----------------- 2
6 ----------------- 1713771 --------- 2 ----------------- 2

And what I was expecting, was for the kickback table.
kickbackID ------ maxID ------- campaignID --- kickbacktypeID
1 ----------------- 1760223 --------- 2 ----------------- 2
2 ----------------- 1709716 --------- 2 ----------------- 2
3 ----------------- 1713771 --------- 2 ----------------- 2
So you can see that I expected 3 new records in the kickback table, and ended up with 6 ( the 3 inserted twice).

Thanks again,
Don

[dmacman1962]

I changed these queries (and added the maxID column to the tempaddreses table) and then used these 2 queries (in Navicat as SQL queries):

UPDATE tempaddresses,teachers
SET tempaddresses.maxID=teachers.maxID
WHERE teachers.EmailAddress=tempaddresses.EmailAddress

...to mark the records in the temp table with the maxID's from the teachers table and it updated the 3 records in the temp table with the correct ID's.

Then I ran this one...

INSERT INTO kickback
(kickback.maxID, kickback.campaignID, kickback.kickbacktypeID, kickback.kbDate)
SELECT  tempaddresses.maxID,  tempaddresses.campaignID,  tempaddresses.kickbacktypeID, tempaddresses.Date
FROM tempaddresses

... and it inserted only 3 rows in the kickback table.

Yet, if I execute these on a php page, it updates 3 records, and then insert 6 (2 duplicate sets of rows) in the kickback table.

The same issue I had with the previous queries?

I checked the page and it is not looping thru the second query.

Anyone ever see this before? The same exact queries work in Navicat, but not a PHP page.

Thanks,
Don

[Sxooter]

dmacman1962;10894679 wrote:

My friend is a SQL programmer and looked at all my queries and reduced it to one query. But when I run it, it inserts the records, and then re-inserts them causing duplicates.

First and foremost, the proper defense against inserting duplicates is usually a unique index on the proper fields in your db. After that, the code will just throw errors instead of mucking up the db with duplicates.

[dmacman1962]

I thought of that, but I cant make any of these fields unique, since they might be several instances of their data (IE it kickbacks on one campaign, but not another) so I need to track them which would not be possible (as far as I understand) if I make them a unique field, therefore eliminating the possibility of duplicate maxID's.

Am I correct, or am I looking at this totally off base?

Thanks,
Don

[Roger_Ramjet]

So yu need to use a multi-column unique index.

[dmacman1962]

Thanks rr,

I did what you suggested and I figured out what it was doing this.

We use an emailaddress extractor program to go thru all the kickbacks that come into outlook. For some reason (probably a bad address format) it created blank 'null' records. These would cause it to 'hang' on the inserts, and create dozens if not hundreds to thousands of dupes for the next record.

When I removed the blank records and re-ran it, it worked as planned.

But I followed your advice, and removed the ID field (which was a PK and not really needed) and set the emailaddress field to Unique and it works great.

Thanks to all,

Don