Perplexing Queries

I am splitting a table in a MySQL database into 2 separate tables. The code below reads values from the first table (departments), then writes those values to a new tables (programs), and then updates the departments table with the new PK (for later parsing).

Simple enough, huh? Ok, here is the code:

<cfquery name="getProg" datasource="#application.dsn#">
SELECT *
FROM departments
WHERE isProgram = 1
</cfquery>


<cfloop query="getProg">

<cftransaction>

<!--- first enter into programs table --->
<cfquery name="saveProg" datasource="soenews">
INSERT INTO programs (name, created_date)
VALUES ('#getProg.name#', #Now()#)
</cfquery>

<!--- get new PK from programs table--->
<cfquery name="getID" datasource="soenews">
SELECT MAX(program_id) AS prog_id
FROM programs
</cfquery>

</cftransaction>

<!--- update departmetns table with new PK --->
<cfquery name="updateDept" datasource="#application.dsn#">
UPDATE departments
SET program_id = #getID.prog_id#
WHERE dept_id = #getProg.dept_id#
</cfquery>

<cfoutput>
Inserted #getProg.name# ID#getID.prog_id# at #Now()# <br />
</cfoutput>
</cfloop>

So what is the problem?

No matter what I do, this code produces duplicate entries in the programs table! In other words, each row from getProg is entered into Programs table twice. They are not duplicated AABBCCDDEE, but ABCDEABCDE.

When it runs, it outputs correctly: 'Inserted Art Education ID205', etc... there is no duplicates in the text output, just in what is written to the database. Text output reports 36 entries, but Proigrams table now has 72 rows.

It's a MySQL database, no triggers or anything like that...

Why, oh why is it doing this?!?!?!?!

 

I'm not sure why its duplicating. But I would probably use a select to insert the values instead of a cfloop. Something like this (queries are not tested)


INSERT INTO Programs (Name, Created_Date)
SELECT DISTINCT Name
FROM Departments
WHERE IsProgram = 1

UPDATE Departments d INNER JOIN Programs p
ON d.Name = p.Name
SET d.dept_id = p.program_id
WHERE d.IsProgram = 1

 

Agreed - that is much cleaner code. I did it the way I did because this is a 'one-time' use code. Once I make this change to the db this code will be trashed. I was working through it step by step, and CFDUMPing and CFABORTing between each to be sure I was pulling the correct information.

Even at that, database backups have been my friend...

A bit OT, but I'm really missing the @@IDENTITY function from MSSQL....

 

Yes, I hear you. I would still use the select distinct and cfdump it afterwards Just a matter of personal preference I guess. But back to your case ..

The query doesn't use DISTINCT. So I could see how records might duplicate .. if department contained multiple records for the same program name. What I don't understand is why you're not seeing duplicates in the output. Any chance the loop is inside some other code?

If you're using MySQL 5, check out
http://dev.mysql.com/doc/refman/5.0/en/getting-unique-id.html

 

Agree, select distinct is the key here.

Just curious...does MySQL have anything like 'set rowcount =...' in SQL Server?

 

MySql has LIMIT

SELECT *
FROM TheTable
LIMIT 10

... or ..

SELECT *
FROM TheTable
LIMIT 10, 20

 

Hey Guys -

Sorry for the late reply work is getting kinda hectic these days.

Aight, not sure if this'll work but just kinda knocked it out. I haven't even gotten a chance to test it but thought it best to share to point in a direction. Here's what I came up with:

<cfquery ....>
Insert into Programs (Name, Created_Date)
Select Name, #CreateODBCDateTime(now())# From Departments where isProgram = 1
</cfquery>
<cfquery>
Update departments
set program_id = (select top 1 program_id from programs where programs.name = departments.name)
</cfquery>

Try it and let me know if it works