Solved

Coldfusion - MySQL Update question

Posted on 2011-09-13
25
385 Views
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.

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

0
Comment
Question by:Bang-O-Matic
  • 11
  • 8
  • 5
  • +1
25 Comments
 
LVL 52

Expert Comment

by:_agx_
Comment Utility
>> 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
 
LVL 39

Expert Comment

by:gdemaria
Comment Utility

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

Author Comment

by:Bang-O-Matic
Comment Utility
could you give me a quick code example?
0
 
LVL 52

Expert Comment

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

Expert Comment

by:_agx_
Comment Utility
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
 

Author Comment

by:Bang-O-Matic
Comment Utility
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
 
LVL 52

Expert Comment

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

Accepted Solution

by:
_agx_ earned 350 total points
Comment Utility
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
 

Author Comment

by:Bang-O-Matic
Comment Utility
"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
 
LVL 52

Expert Comment

by:_agx_
Comment Utility
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
 

Author Comment

by:Bang-O-Matic
Comment Utility
thanks agx, will that syntax work with MySQL as well?
0
 
LVL 59

Assisted Solution

by:Kevin Cross
Kevin Cross earned 150 total points
Comment Utility
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
How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

 
LVL 52

Expert Comment

by:_agx_
Comment Utility
Thanks mwvisa1 :)
0
 

Author Comment

by:Bang-O-Matic
Comment Utility
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
 
LVL 59

Expert Comment

by:Kevin Cross
Comment Utility
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
 

Author Comment

by:Bang-O-Matic
Comment Utility
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
 
LVL 52

Expert Comment

by:_agx_
Comment Utility
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
 
LVL 59

Expert Comment

by:Kevin Cross
Comment Utility
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
 

Author Comment

by:Bang-O-Matic
Comment Utility
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
 
LVL 52

Expert Comment

by:_agx_
Comment Utility
@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
 

Author Comment

by:Bang-O-Matic
Comment Utility
I ran all 3 couldn't tell any difference, but my table only has around 4k records.
0
 
LVL 59

Expert Comment

by:Kevin Cross
Comment Utility
@_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
 
LVL 52

Expert Comment

by:_agx_
Comment Utility
@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
 
LVL 59

Expert Comment

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

Expert Comment

by:_agx_
Comment Utility
lol. Ok, but no tromping around in my sandbox until my sand castle is finished ;-)
0

Featured Post

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Hi, Even though I have created this Tutorial on My personal Blog, Some people might not able to find my website, So here i am posting it again Today, from the topic it is very clear that i will be showing you here the very basic usage of how we …
I spent nearly three days trying to figure out how incorporate OAuth in Coldfusion for the Eventful API. Hopefully, this article will allow Coldfusion Programmers to buzz through the API when they need to. Basically, what this script does is authori…
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.

772 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now