asked on

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

_agx_

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

gdemaria

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

Dusty

ASKER

could you give me a quick code example?

_agx_

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

_agx_

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.

Dusty

ASKER

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.

_agx_

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

ASKER CERTIFIED SOLUTION

_agx_

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Dusty

ASKER

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

_agx_

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

Dusty

ASKER

thanks agx, will that syntax work with MySQL as well?

SOLUTION

Kevin Cross

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

_agx_

Thanks mwvisa1 :)

Dusty

ASKER

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!

Kevin Cross

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!

Dusty

ASKER

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

_agx_

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

Kevin Cross

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

Dusty

ASKER

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!

_agx_

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

Dusty

ASKER

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

Kevin Cross

@_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

_agx_

@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!

Kevin Cross

Thanks! You did and always do also. See you around the playground. :)

_agx_

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