Hello all,
Anybody have MySql skills? Our reqular guy is out for a couple of weeks so I'm trying to help out. I am mainly a UI guy but I am enjoying CF. Ok to the question. I am trying to update a table with another table. I want to find any duplicate rows based on a particular field, replace them and then add any new rows. Here is what I have (the error that it returns is below):
<cfquery datasource="square" name="loadupdates">
SELECT MLS_Id, List_Price, Public_Address, Street_Number, Street_Name, Unit_Number, City, Zip_Code, Subdivision_Name, Bedroom, Baths_Total, Full_Baths, Half_Baths, Square_Feet, Lot_Sqft, Garage_Capacity, Garage_Type, High_School, Junior_School, Property_Description, Listing_Office_Name, Listing_Office_Id, Listing_Agent_Name, Listing_Agent_Phone, Listing_Agent_Id, Short_Sale, Open_House_Flag, List_Date, Last_Image_Update, Price_Change_Date, Image_Count, Latitude, Longitude
FROM square.glvar_daily
</cfquery>
<cfloop query="loadupdates">
<cfquery name="mergUpdates" datasource="square">
INSERT INTO square.glvar (MLS_Id, List_Price, Public_Address, Street_Number, Street_Name, Unit_Number, City, Zip_Code, Subdivision_Name, Bedroom, Baths_Total, Full_Baths, Half_Baths, Square_Feet, Lot_Sqft, Garage_Capacity, Garage_Type, High_School, Junior_School, Property_Description, Listing_Office_Name, Listing_Office_Id, Listing_Agent_Name, Listing_Agent_Phone, Listing_Agent_Id, Short_Sale, Open_House_Flag, List_Date, Last_Image_Update, Price_Change_Date, Image_Count, Latitude, Longitude)
VALUES (#loadupdates.MLS_Id#,#loadupdates.List_Price#,#loadupdates.Public_Address#,#loadupdates.Street_Number#,#loadupdates.Street_Name#,#loadupdates.Unit_Number#,#loadupdates.City#,#loadupdates.Zip_Code#,#loadupdates.Subdivision_Name#,#loadupdates.Bedroom#,#loadupdates.Baths_Total#,#loadupdates.Full_Baths#,#loadupdates.Half_Baths#,#loadupdates.Square_Feet#,#loadupdates.Lot_Sqft#, #loadupdates.Garage_Capacity#,#loadupdates.Garage_Type#,#loadupdates.High_School#,#loadupdates.Junior_School#,#loadupdates.Property_Description#,#loadupdates.Listing_Office_Name#,#loadupdates.Listing_Office_Id#, #loadupdates.Listing_Agent_Name#,#loadupdates.Listing_Agent_Phone#,#loadupdates.Listing_Agent_Id#,#loadupdates.Short_Sale#,#loadupdates.Open_House_Flag#,#loadupdates.List_Date#,#loadupdates.Last_Image_Update#,#loadupdates.Price_Change_Date#,#loadupdates.Image_Count#,#loadupdates.Latitude#,#loadupdates.Longitude#)
ON DUPLICATE KEY UPDATE MLS_Id=MLS_Id+#loadupdates.MLS_Id#;
UPDATE square.glvar SET MLS_Id=MLS_Id+#loadupdates.MLS_Id#
WHERE
(MLS_Id = #loadupdates.MLS_Id#,List_Price = #loadupdates.List_Price#,Public_Address = #loadupdates.Public_Address#,Street_Number = #loadupdates.Street_Number#,Street_Name = #loadupdates.Street_Name#,Unit_Number = #loadupdates.Unit_Number#,City = #loadupdates.City#,Zip_Code = #loadupdates.Zip_Code#,Subdivision_Name = #loadupdates.Subdivision_Name#,Bedroom = #loadupdates.Bedroom#,Baths_Total = #loadupdates.Baths_Total#,Full_Baths = #loadupdates.Full_Baths#,Half_Baths = #loadupdates.Half_Baths#,Square_Feet = #loadupdates.Square_Feet#,Lot_Sqft = #loadupdates.Lot_Sqft#,Garage_Capacity = #loadupdates.Garage_Capacity#,Garage_Type = #loadupdates.Garage_Type#,High_School = #loadupdates.High_School#,Junior_School = #loadupdates.Junior_School#,Property_Description = #loadupdates.Property_Description#,Listing_Office_Name = #loadupdates.Listing_Office_Name#,Listing_Office_Id = #loadupdates.Listing_Office_Id#,Listing_Agent_Name = #loadupdates.Listing_Agent_Name#,Listing_Agent_Phone = #loadupdates.Listing_Agent_Phone#,Listing_Agent_Id = #loadupdates.Listing_Agent_Id#,Short_Sale = #loadupdates.Short_Sale#,Open_House_Flag = #loadupdates.Open_House_Flag#,List_Date = #loadupdates.List_Date#,Last_Image_Update = #loadupdates.Last_Image_Update#,Price_Change_Date = #loadupdates.Price_Change_Date#,Image_Count = #loadupdates.Image_Count#,Latitude = #loadupdates.Latitude#,Longitude = #loadupdates.Longitude#;)
</cfquery>
</cfloop>
Here is the error:
Error Executing Database Query. |
| 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 'ALORA ST,Street_Number = 11188,Street_Name = ALORA ST,Unit_Number = ,City = Las ' at line 3 |
|
The error occurred in C:/Program Files/Apache Software Foundation/Apache2.2/htdocs/squaretortilla/Feeds/GLVAR/parseCSV.cfm: line 78
|
76 : WHERE 77 : 78 : MLS_Id = #loadupdates.MLS_Id#,List_Price = #loadupdates.List_Price#,Public_Address = #loadupdates.Public_Address#,Street_Number = #loadupdates.Street_Number#,Street_Name = #loadupdates.Street_Name#,Unit_Number = #loadupdates.Unit_Number#,City = #loadupdates.City#,Zip_Code = #loadupdates.Zip_Code#,Subdivision_Name = #loadupdates.Subdivision_Name#,Bedroom = #loadupdates.Bedroom#,Baths_Total = #loadupdates.Baths_Total#,Full_Baths = #loadupdates.Full_Baths#,Half_Baths = #loadupdates.Half_Baths#,Square_Feet = #loadupdates.Square_Feet#,Lot_Sqft = #loadupdates.Lot_Sqft#,Garage_Capacity = #loadupdates.Garage_Capacity#,Garage_Type = #loadupdates.Garage_Type#,High_School = #loadupdates.High_School#,Junior_School = #loadupdates.Junior_School#,Property_Description = #loadupdates.Property_Description#,Listing_Office_Name = #loadupdates.Listing_Office_Name#,Listing_Office_Id = #loadupdates.Listing_Office_Id#,Listing_Agent_Name = #loadupdates.Listing_Agent_Name#,Listing_Agent_Phone = #loadupdates.Listing_Agent_Phone#,Listing_Agent_Id = #loadupdates.Listing_Agent_Id#,Short_Sale = #loadupdates.Short_Sale#,Open_House_Flag = #loadupdates.Open_House_Flag#,List_Date = #loadupdates.List_Date#,Last_Image_Update = #loadupdates.Last_Image_Update#,Price_Change_Date = #loadupdates.Price_Change_Date#,Image_Count = #loadupdates.Image_Count#,Latitude = #loadupdates.Latitude#,Longitude = #loadupdates.Longitude#; 79 : 80 : |
AdChoices