Coldfusion - MySQL Update question

Posted on 2011-09-13
Medium Priority
Last Modified: 2012-05-12
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.


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

DELETE FROM listings

<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)


<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#">


Open in new window

Question by:Bang-O-Matic
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 11
  • 8
  • 5
  • +1
LVL 52

Expert Comment

ID: 36532436
>> 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.  
LVL 39

Expert Comment

ID: 36532493

..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.

Author Comment

ID: 36532564
could you give me a quick code example?
Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

LVL 52

Expert Comment

ID: 36532614
Bang-O-Matic - I'm definitely missing something.  Refresh my memory. What's the purpose of your delete cascade in plain english?
LVL 52

Expert Comment

ID: 36532672
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.

Author Comment

ID: 36532691
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.
LVL 52

Expert Comment

ID: 36532723
Makes sense.  But you DO want to delete the user_fav records if a listing is no longer in your import file, correct?
LVL 52

Accepted Solution

_agx_ earned 1400 total points
ID: 36532807
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 --->
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

FROM    listing l LEFT JOIN listingWorkTable w ON w.ListID = l.ListID
WHERE w.ListID is NULL <!--- id does not exist in WORK table --->

Author Comment

ID: 36532875
"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.
LVL 52

Expert Comment

ID: 36533090
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

Author Comment

ID: 36533184
thanks agx, will that syntax work with MySQL as well?
LVL 60

Assisted Solution

by:Kevin Cross
Kevin Cross earned 600 total points
ID: 36533403
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, .....


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

In other words, the JOIN happens in the UPDATE clause itself.
LVL 52

Expert Comment

ID: 36533490
Thanks mwvisa1 :)

Author Comment

ID: 36536309
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

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?

LVL 60

Expert Comment

by:Kevin Cross
ID: 36536376
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 

Open in new window

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

Hope that helps!

Author Comment

ID: 36537403
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

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
LVL 52

Expert Comment

ID: 36537444
For mySQL I think you just need to add a table alias after the delete

FROM    Listings l LEFT JOIN wListings w ON w.ListID = l.ListID
LVL 60

Expert Comment

by:Kevin Cross
ID: 36537548
Correct! And as an aside, since you are not deleting from both tables you may want to try this:
FROM Listings l 
   FROM wListings w 
   WHERE w.ListID = l.ListID

Open in new window


FROM Listings l 
   SELECT w.ListID
   FROM wListings w 

Open in new window


Author Comment

ID: 36537595
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!
LVL 52

Expert Comment

ID: 36537596
@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.

Author Comment

ID: 36537656
I ran all 3 couldn't tell any difference, but my table only has around 4k records.
LVL 60

Expert Comment

by:Kevin Cross
ID: 36538008
@_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,
LVL 52

Expert Comment

ID: 36538124
@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!
LVL 60

Expert Comment

by:Kevin Cross
ID: 36538173
Thanks! You did and always do also. See you around the playground. :)
LVL 52

Expert Comment

ID: 36538186
lol. Ok, but no tromping around in my sandbox until my sand castle is finished ;-)

Featured Post

Setting up LaraDock for Laravel

Learn how to set up LaraDock in a Laravel project - LaraDock gives us an easy way to run a Laravel application using Docker in a single command.

Question has a verified solution.

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

In this blog, we’ll look at how improvements to Percona XtraDB Cluster improved IST performance.
Backups and Disaster RecoveryIn this post, we’ll look at strategies for backups and disaster recovery.
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…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses

762 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