Coldfusion - MySQL Update question

Im using Windows server/ Coldfusion 9 / MySQL 5.5

I have a MySQL database of real estate listing and I get updates to this database daily (tab delimited text files) in order to keep the data fresh,  I have been just deleting all records in the table and then reading from the daily text file and parsing all the records back into the table line by line.  see attached code.

Because I have an app running on this site that has foreign key constraints with ON DELETE CASCADE I can no longer delete all the records in the table.

What I need to do is just Update the records without deleting them, and then delete only the records that are not included in the daily (updated) text file. I'm looking for ideas on the best way to handle this.

Thanks!

<cfquery name="qry1" datasource="FOF">

DELETE FROM listings
</cfquery>

<cffile action="read"  file="#ExpandPath( '/rets/residential.txt' )#"  variable="txtFile"><cfset endLine = chr(13)&chr(10)>
<cfset txtFile = replace(txtFile, endLine, chr(10), "all")>
<cfset txtFile = replace(txtFile, chr(13)&chr(10), "", "all")>
<cfset rowCounter = 0>
                        
<cfloop index="record" list="#txtFile#" delimiters="#chr(13)##chr(10)#">
    <cfset rowCounter = rowCounter + 1>                              
     <cfif rowCounter neq 1>
        <cfset rec = listToArray(record,chr(9),true) >
    
<cfif arrayLen(rec) eq 54>
    <cfset ListID = trim(rec[1])>
    <cfset PropType = trim(rec[2])>
    <cfset Status = trim(rec[3])>
    <cfset Price = trim(rec[4])>
    <cfset StreetNumber = trim(rec[5])>
    <cfset StreetName = trim(rec[6])>
    <cfset City = trim(rec[7])>
    <cfset State = trim(rec[8])>
    <cfset Zip = trim(rec[9])>
    <cfset Subdivision = trim(rec[10])>
    <cfset Bedrooms = trim(rec[11])>
    <cfset FullBaths = trim(rec[12])>
    <cfset Halfbaths = trim(rec[13])>
    <cfset TotalBaths = trim(rec[14])>
    <cfset Stories = trim(rec[15])>
    <cfset Acreage = trim(rec[16])>
    <cfset ListAgentID = trim(rec[17])>
    <cfset ListOfficeID = trim(rec[18])>
    <cfset YearBuilt = trim(rec[19])>
    <cfset Construction = trim(rec[20])>
    <cfset Directions = trim(rec[21])>
    <cfset Remarks = trim(rec[22])>
    <cfset ListDate = trim(rec[23])>
    <cfset DateofLastChange = trim(rec[24])>
    <cfset CoListAgentID = trim(rec[25])>
    <cfset CoListOfficeID = trim(rec[26])>
    <cfset Energy = trim(rec[27])>
    <cfset ExtFeatures = trim(rec[28])>
    <cfset FeesInclude = trim(rec[29])>
    <cfset Ownership = trim(rec[30])>
    <cfset HoaFee = trim(rec[31])>
    <cfset HoaTerm = trim(rec[32])>
    <cfset ImmediateOccupancy = trim(rec[33])>
    <cfset IntFeatures = trim(rec[34])>
    <cfset LotAccess = trim(rec[35])>
    <cfset LotDimensions = trim(rec[36])>
    <cfset MainArea = trim(rec[37])>
    <cfset ParcelID = trim(rec[38])>
    <cfset Parkingfeatures = trim(rec[39])>
    <cfset ParkingType = trim(rec[40])>
    <cfset PhotoCount = trim(rec[41])>
    <cfset PhotoDateModified = trim(rec[42])>
    <cfset ProjectFacilities = trim(rec[43])>
    <cfset ProjectName = trim(rec[44])>
    <cfset PublicShowAddress = trim(rec[45])>
    <cfset SqftHVAC = trim(rec[46])>
    <cfset SubArea = trim(rec[47])>
    <cfset UnitNumber = trim(rec[48])>
    <cfset Utilities = trim(rec[49])>
    <cfset WaterFrontage = trim(rec[50])>
    <cfset WaterFront = trim(rec[51])>
    <cfset WaterView = trim(rec[52])>
    <cfset Zoning = trim(rec[53])>
    <cfset Photos = trim(rec[54])>
    
    
    <cfquery name="qryInsert" datasource="FOF">

INSERT INTO listings (ListID, PropType, Status, Price, StreetNumber, StreetName, City, State, Zip, Subdivision, Bedrooms, FullBaths, HalfBaths, TotalBaths, Stories, Acreage, ListAgentID, ListOfficeID, YearBuilt, Construction, Directions, Remarks, ListDate, DateofLastChange, CoListAgentID, CoListOfficeID, Energy, ExtFeatures, FeesInclude, Ownership, HoaFee, HoaTerm, ImmediateOccupancy, IntFeatures, LotAccess, LotDimensions, MainArea, ParcelID, Parkingfeatures, ParkingType, PhotoCount, PhotoDateModified, ProjectFacilities, ProjectName, PublicShowAddress, SqftHVAC, SubArea, UnitNumber, Utilities, WaterFrontage, WaterFront, WaterView, Zoning, Photos)

VALUES (

<cfqueryparam cfsqltype="CF_SQL_INTEGER" value="#ListID#" null="#NOT isNumeric(ListID)#">,
<cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#PropType#">,
<cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#Status#">,
<cfqueryparam cfsqltype="CF_SQL_INTEGER" value="#Price#" null="#NOT isNumeric(Price)#">,
<cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#StreetNumber#">,
<cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#StreetName#">,
<cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#City#">,
<cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#State#">,
<cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#Zip#">,
<cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#Subdivision#">,
<cfqueryparam cfsqltype="CF_SQL_INTEGER" value="#Bedrooms#" null="#NOT isNumeric(Bedrooms)#">,
<cfqueryparam cfsqltype="CF_SQL_INTEGER" value="#FullBaths#" null="#NOT isNumeric(FullBaths)#">,
<cfqueryparam cfsqltype="CF_SQL_INTEGER" value="#HalfBaths#" null="#NOT isNumeric(HalfBaths)#">,
<cfqueryparam cfsqltype="CF_SQL_INTEGER" value="#TotalBaths#" null="#NOT isNumeric(TotalBaths)#">,
<cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#Stories#">,
<cfqueryparam cfsqltype="CF_SQL_INTEGER" value="#Acreage#" null="#NOT isNumeric(Acreage)#">,
<cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#ListAgentID#">,
<cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#ListOfficeID#">,
<cfqueryparam cfsqltype="CF_SQL_INTEGER" value="#YearBuilt#" null="#NOT isNumeric(YearBuilt)#">,
<cfqueryparam cfsqltype="CF_SQL_LONGVARCHAR" value="#Construction#">,
<cfqueryparam cfsqltype="CF_SQL_LONGVARCHAR" value="#Directions#">,
<cfqueryparam cfsqltype="CF_SQL_LONGVARCHAR" value="#Remarks#">,
<cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#ListDate#">,
<cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#DateofLastChange#">,
<cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#CoListAgentID#">,
<cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#CoListOfficeID#">,
<cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#Energy#">,
<cfqueryparam cfsqltype="CF_SQL_LONGVARCHAR" value="#ExtFeatures#">,
<cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#FeesInclude#">,
<cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#Ownership#">,
<cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#HoaFee#">,
<cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#HoaTerm#">,
<cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#ImmediateOccupancy#">,
<cfqueryparam cfsqltype="CF_SQL_LONGVARCHAR" value="#IntFeatures#">,
<cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#LotAccess#">,
<cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#LotDimensions#">,
<cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#MainArea#">,
<cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#ParcelID#">,
<cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#ParkingFeatures#">,
<cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#ParkingType#">,
<cfqueryparam cfsqltype="CF_SQL_INTEGER" value="#PhotoCount#" null="#NOT isNumeric(PhotoCount)#">,
<cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#PhotoDateModified#">,
<cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#ProjectFacilities#">,
<cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#ProjectName#">,
<cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#PublicShowAddress#">,
<cfqueryparam cfsqltype="CF_SQL_INTEGER" value="#SqftHVAC#" null="#NOT isNumeric(SqftHVAC)#">,
<cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#SubArea#">,
<cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#UnitNumber#">,
<cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#Utilities#">,
<cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#WaterFrontage#">,
<cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#WaterFront#">,
<cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#Waterview#">,
<cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#Zoning#">,
<cfqueryparam cfsqltype="CF_SQL_LONGVARCHAR" value="#Photos#">
)

 </cfquery>
  </cfif>
  </cfif>
</cfloop>

Open in new window

Bang-O-MaticAsked:
Who is Participating?
 
_agx_Connect With a Mentor Commented:
Personally I prefer using work tables for imports. They're more flexible and let you work around issues like this.  The "work" table structure mirrors the target table (ie listings). All data is inserted into the work table first. Then you can easily detect new/deleted/modified records with a join.

This is untested MS SQL syntax, but to identify "new" records, I use an outer join

INSERT INTO MainTable ( ListID, PropType, ...)
SELECT w.ListID, w.PropType, ...
FROM    listingWorkTable w LEFT JOIN listing l ON l.ListID = w.ListID
WHERE l.ListID is NULL <!--- id does not exist in main table --->

To identify modified records is a simple inner join

<!--- existing listing --->
UPDATE m
SET       m.PropType = w.PropType, .....
FROM    listingWorkTable w INNER JOIN listing l ON l.ListID = w.ListID

To identify deleted records is another outer join


DELETE FROM l
FROM    listing l LEFT JOIN listingWorkTable w ON w.ListID = l.ListID
WHERE w.ListID is NULL <!--- id does not exist in WORK table --->
0
 
_agx_Commented:
>> then delete only the records that are not included in the daily (updated) text file

Wouldn't you still have the same problem, just on a smaller scale? If the error is occurring because you're trying to delete an ID that's in use, you'd have to delete the FK records first. Only then can delete ones in the primary table.  
0
 
gdemariaCommented:

..to perform an update instead of an insert, you have to determine the unique key of the record you're importing from the file and then update that record in the database (if it exists), if it doesn't exist, you would then insert it.
0
Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

 
Bang-O-MaticAuthor Commented:
could you give me a quick code example?
0
 
_agx_Commented:
Bang-O-Matic - I'm definitely missing something.  Refresh my memory. What's the purpose of your delete cascade in plain english?
0
 
_agx_Commented:
Gah... hit return too soon.  

I was going to say, the reason for asking is because I'm not sure why you're even getting the error in the 1st place with a cascade. So maybe I'm misunderstanding something.
0
 
Bang-O-MaticAuthor Commented:
I have a save as favs app where once logged in to the site you can view property listings and then save them as favs. It conssists of 2 tables (users,user_favs) in the user_favs table one of the fields (prop_fk) has a foreign key "ListID" which is the Primary Key for my "listings" table. If I set the On delete to cascade, then when I delete all the records in my listings table and insert the new records, all the properties I had saved as favs are also deleted. If I set prop_fk to On Delete No Action or anything beside Cascade, I get an error when I try to run the code I attached.
0
 
_agx_Commented:
Makes sense.  But you DO want to delete the user_fav records if a listing is no longer in your import file, correct?
0
 
Bang-O-MaticAuthor Commented:
"But you DO want to delete the user_fav records if a listing is no longer in your import file, correct? " Yes I do. Thats why I was thinking I should Update the records without deleting them, and then delete only the records that are not included in the daily (updated) text file. Because the only records that wouldnt be included in the text file are properties that have gone SOLD, Off the market, expired, etc...and I want those deleted from my user_favs table as well.
0
 
_agx_Commented:
Yep, that's how you'd need to approach it.  Technically you could identify the new/modified records row-by-row within your loop. I think that's what gd was suggesting.  Though it'd require about 2 queries for every record.  The other problem would be identifying deleted records.  

That's where the "work table" approach is better.  You truncate it and populate it just like you're doing now. Then all you need is the 3 queries I posted to

- insert new records
- update existing records
- delete removed listings
0
 
Bang-O-MaticAuthor Commented:
thanks agx, will that syntax work with MySQL as well?
0
 
Kevin CrossConnect With a Mentor Chief Technology OfficerCommented:
It looks like _agx_ has answered your question, but just remember in MySQL you can delete from multiple tables at once http://dev.mysql.com/doc/refman/5.5/en/delete.html -- I would recommend the staged approached as suggested, but just in case you need to truly cascade.

Additionally, UPDATE with JOIN in MySQL looks like this:
UPDATE  MainTable m
INNER JOIN listingWorkTable w ON m.ListID
INNER JOIN listing l ON l.ListID = w.ListID
SET m.PropType = w.PropType, .....

Or

UPDATE MainTable m, listing l
SET ...
WHERE l.ListID = m.ListID

In other words, the JOIN happens in the UPDATE clause itself.
0
 
_agx_Commented:
Thanks mwvisa1 :)
0
 
Bang-O-MaticAuthor Commented:
Thanks guys! I setup another (worktable)  wListings, then populated it and ran the following update query:

<cfquery name="update" datasource = "FOF">
UPDATE  Listings m
INNER JOIN wListings w ON m.ListID
INNER JOIN Listings l ON l.ListID = w.ListID
SET m.PropType = w.PropType, m.Status = w.Status, m.Price =w.Price, m.StreetNumber = w.StreetNumber, m.StreetName = w.StreetName, m.City = w.City
</cfquery>

The query ran without error but all it did was change all fields that I included in the SET to the same values, so all my streetnames, streetnumbers, cities are the same. Can you tell me where I went wrong?

Thanks!
0
 
Kevin CrossChief Technology OfficerCommented:
There is a type-o (my apologies, not feeling well at the moment, so increases the normal mistype rate :)):

 INNER JOIN wListings w ON m.ListID

I stopped mid thought there ... You need to complete the JOIN criteria to properly link the worktable(s) to your MainTable. By the way, if Listings is you main table, then this is redundant anyway. Here is what you need.

<cfquery name="update" datasource = "FOF">
UPDATE  Listings l
INNER JOIN wListings w ON l.ListID = w.ListID
SET l.PropType = w.PropType, l.Status = w.Status, l.Price =w.Price
   , l.StreetNumber = w.StreetNumber, l.StreetName = w.StreetName, l.City = w.City 
;
</cfquery>

Open in new window


INNER JOIN wListings w ON l.ListID = w.ListID

Hope that helps!
0
 
Bang-O-MaticAuthor Commented:
Thanks for all your help! I got the Update query and the Insert query working but the delete query is not working, can you tell me where the problem is?

<cfquery name="delete" datasource = "FOF">
DELETE FROM Listings l LEFT JOIN wListings w ON w.ListID = l.ListID
WHERE w.ListID is NULL
</cfquery>


heres the error:

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 'l LEFT JOIN wListings w ON w.ListID = l.ListID WHERE w.ListID is NULL' at line 1
0
 
_agx_Commented:
For mySQL I think you just need to add a table alias after the delete

ie
DELETE l
FROM    Listings l LEFT JOIN wListings w ON w.ListID = l.ListID
WHERE w.ListID is NULL
0
 
Kevin CrossChief Technology OfficerCommented:
Correct! And as an aside, since you are not deleting from both tables you may want to try this:
DELETE l 
FROM Listings l 
WHERE NOT EXISTS (
   SELECT 1
   FROM wListings w 
   WHERE w.ListID = l.ListID
);

Open in new window


Or:

DELETE l 
FROM Listings l 
WHERE l.ListID NOT IN (
   SELECT w.ListID
   FROM wListings w 
);

Open in new window

0
 
Bang-O-MaticAuthor Commented:
Yes that worked! In looking back through your comments I see that that's how you suggested it be done in the first place....my bad. It's all working now and I certainly appreciate the help!
0
 
_agx_Commented:
@mwvisa1 - Out of curiosity have you profiled the 3 to see which is the most efficient? My initial guess would be the NOT EXISTS version. Followed by the LEFT JOIN and lastly the NOT IN version.  Not sure if the IS NULL would change anything.
0
 
Bang-O-MaticAuthor Commented:
I ran all 3 couldn't tell any difference, but my table only has around 4k records.
0
 
Kevin CrossChief Technology OfficerCommented:
@_agx_: yes, that is always my ranking of those. In truth, the query optimizer may treat these as the same pattern and for certain sets of data perform exactly the same. With the NOT EXISTS short-circuiting, that is my preferred way. LEFT JOIN can take advantage of index if one exists, so that is second. NOT IN has to check against every value in the IN clause in theory before finally determining that your values doesn't exist; therefore, in theory that is the worst. The optimizer is the great equalizer though as it may see the NOT IN pattern as the same as NOT EXISTS and under the covers change it to that.

Bang-O-Matic: thanks for including me in the point distribution. I am just glad I could add some helpful comments.

Best regards and happy coding,
Kevin
0
 
_agx_Commented:
@mwvisa1 - Makes sense.  When I get some time, I'll have to check the execution plan to see how it works out :)

>> Bang-O-Matic: thanks for including me in the point distribution

Well earned. You provided some great tips!
0
 
Kevin CrossChief Technology OfficerCommented:
Thanks! You did and always do also. See you around the playground. :)
0
 
_agx_Commented:
lol. Ok, but no tromping around in my sandbox until my sand castle is finished ;-)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.