Solved

Coldfusion - MySQL Update question

Posted on 2011-09-13
25
399 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
[X]
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
25 Comments
 
LVL 52

Expert Comment

by:_agx_
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.  
0
 
LVL 39

Expert Comment

by:gdemaria
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.
0
 

Author Comment

by:Bang-O-Matic
ID: 36532564
could you give me a quick code example?
0
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 
LVL 52

Expert Comment

by:_agx_
ID: 36532614
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_
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.
0
 

Author Comment

by:Bang-O-Matic
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.
0
 
LVL 52

Expert Comment

by:_agx_
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?
0
 
LVL 52

Accepted Solution

by:
_agx_ earned 350 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 --->
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
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.
0
 
LVL 52

Expert Comment

by:_agx_
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
0
 

Author Comment

by:Bang-O-Matic
ID: 36533184
thanks agx, will that syntax work with MySQL as well?
0
 
LVL 60

Assisted Solution

by:Kevin Cross
Kevin Cross earned 150 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, .....

Or

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

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

Expert Comment

by:_agx_
ID: 36533490
Thanks mwvisa1 :)
0
 

Author Comment

by:Bang-O-Matic
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
</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 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 
;
</cfquery>

Open in new window


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

Hope that helps!
0
 

Author Comment

by:Bang-O-Matic
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
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_
ID: 36537444
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 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:
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
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!
0
 
LVL 52

Expert Comment

by:_agx_
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.
0
 

Author Comment

by:Bang-O-Matic
ID: 36537656
I ran all 3 couldn't tell any difference, but my table only has around 4k records.
0
 
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,
Kevin
0
 
LVL 52

Expert Comment

by:_agx_
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!
0
 
LVL 60

Expert Comment

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

Expert Comment

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

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Does the idea of dealing with bits scare or confuse you? Does it seem like a waste of time in an age where we all have terabytes of storage? If so, you're missing out on one of the core tools in every professional programmer's toolbox. Learn how to …
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
How to Install VMware Tools in Red Hat Enterprise Linux 6.4 (RHEL 6.4) Step-by-Step Tutorial
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an antispam), the admini…

739 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