0 Replies Latest reply on Aug 28, 2013 9:35 AM by CF_noobi_wan

    MySql merge tables?

    CF_noobi_wan

      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#,#loa dupdates.Zip_Code#,#loadupdates.Subdivision_Name#,#loadupdates.Bedroom#,#loadupdates.Baths _Total#,#loadupdates.Full_Baths#,#loadupdates.Half_Baths#,#loadupdates.Square_Feet#,#loadu pdates.Lot_Sqft#, #loadupdates.Garage_Capacity#,#loadupdates.Garage_Type#,#loadupdates.High_School#,#loadup dates.Junior_School#,#loadupdates.Property_Description#,#loadupdates.Listing_Office_Name#, #loadupdates.Listing_Office_Id#, #loadupdates.Listing_Agent_Name#,#loadupdates.Listing_Agent_Phone#,#loadupdates.Listing_A gent_Id#,#loadupdates.Short_Sale#,#loadupdates.Open_House_Flag#,#loadupdates.List_Date#,#l oadupdates.Last_Image_Update#,#loadupdates.Price_Change_Date#,#loadupdates.Image_Count#,#l oadupdates.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 :