Jump to content

optimising UPDATE CASE QUERY


Greywacke
 Share

Recommended Posts

hi all,i have a query that i have to run to update a new field in the database according to the fitment or Fitment attributes that are already existing in the records. the attributes are all in a string eg w = x<br />y = zhere is my statement - it supposedly retrieves the fitment/Fitment attribute, then does a case when to get a different value depending on the value of the fitment/Fitment attribute. i hope the comments are explanatory enough :)

UPDATE 	6_serviceleads SET 	timestamp_ExpectedBy = 	CASE 		IF(			INSTR(text_LeadAttributes,"canopy_req"), 				-- if QuoteMe (QM) else CanopyXchange (CX)			SUBSTRING_INDEX(							-- get fitment attribute value for QM				SUBSTRING_INDEX(					SUBSTRING_INDEX(						6_serviceleads.text_LeadAttributes, 						'<br />', 						4					), 					';', 					-1				), 				' = ', 				-1			), 			SUBSTRING_INDEX(							-- get Fitment attribute value for CX				SUBSTRING_INDEX(					SUBSTRING_INDEX(						6_serviceleads.text_LeadAttributes, 						'<br />', 						6					), 					';', 					-1				), 				' = ', 				-1			)		) 		WHEN "ASAP" THEN 								-- "ASAP" for both QM and CX			TIMESTAMP(				DATE_ADD(					DATE(timestamp_LeadCreated), 					INTERVAL 7 DAY				), 				"23:59:59"			) 		WHEN "within_2_weeks" THEN 							-- "within_2_weeks" for QM			TIMESTAMP(				DATE_ADD(					DATE(timestamp_LeadCreated), 					INTERVAL 14 DAY				), 				"23:59:59"			) 		WHEN "Within the next 2 weeks" THEN 						-- "Within the next 2 weeks" for CX			TIMESTAMP(				DATE_ADD(					DATE(timestamp_LeadCreated), 					INTERVAL 14 DAY				), 				"23:59:59"			) 		WHEN "within_month" THEN 							-- "within_month" for QM			TIMESTAMP(				DATE_ADD(					DATE(timestamp_LeadCreated), 					INTERVAL 30 DAY				), 				"23:59:59"			) 		WHEN "Within the next month" THEN 						-- "Within the next month" for CX			TIMESTAMP(				DATE_ADD(					DATE(timestamp_LeadCreated), 					INTERVAL 30 DAY				), 				"23:59:59"			) 		WHEN "within_2_months" THEN 							-- "within_2_months" for QM			TIMESTAMP(				DATE_ADD(					DATE(timestamp_LeadCreated), 					INTERVAL 60 DAY				), 				"23:59:59"			) 		WHEN "Within the next 2 months" THEN 						-- "Within the next 2 months" for CX			TIMESTAMP(				DATE_ADD(					DATE(timestamp_LeadCreated), 					INTERVAL 60 DAY				), 				"23:59:59"			) 		WHEN "within_3_months" THEN 							-- "within_3_months" for QM			TIMESTAMP(				DATE_ADD(					DATE(timestamp_LeadCreated), 					INTERVAL 90 DAY				), 				"23:59:59"			) 		WHEN "Within the next 3 months" THEN 						-- "Within the next 3 months" for CX			TIMESTAMP(				DATE_ADD(					DATE(timestamp_LeadCreated), 					INTERVAL 90 DAY				), 				"23:59:59"			) 		WHEN "not_within_3_months" THEN 						-- "not_within_3_months" for QM			TIMESTAMP(				DATE_ADD(					DATE(timestamp_LeadCreated), 					INTERVAL 120 DAY				), 				"23:59:59"			) 		WHEN "Not within the next 3 months" THEN 					-- "Not within the next 3 months" for CX			TIMESTAMP(				DATE_ADD(					DATE(timestamp_LeadCreated), 					INTERVAL 120 DAY				), 				"23:59:59"			) 		ELSE 										-- timestamp with empty date = error			TIMESTAMP("0000-00-00 23:59:59") 	END;

now it seems as if this query made the server crash. i would have to optimise it somehow, but i am not quite sure how. the expectedby timestamp is always by 23:59:59, the date depending on the fitment or Fitment attribute.any advise? any assistance perhaps?

Edited by Pierre 'Greywacke' du Toit
Link to comment
Share on other sites

ok this has been updated a bit by taking only the date part of the timestamp for LeadCreated.rather long query though 0o

UPDATE 	6_serviceleads SET 	timestamp_ExpectedBy = 	CASE 		IF(			INSTR(												-- if QuoteMe (QM) else CanopyXchange (CX)				text_LeadAttributes, 				"canopy_req"			) > -1, 			SUBSTRING_INDEX(										-- get fitment attribute value for QM				SUBSTRING_INDEX(					SUBSTRING_INDEX(						6_serviceleads.text_LeadAttributes, 						'<br />', 						4					), 					';', 					-1				), 				' = ', 				-1			), 			SUBSTRING_INDEX(										-- get Fitment attribute value for CX				SUBSTRING_INDEX(					SUBSTRING_INDEX(						6_serviceleads.text_LeadAttributes, 						'<br />', 						6					), 					';', 					-1				), 				' = ', 				-1			)		) 		WHEN "ASAP" THEN 											-- "ASAP" for both QM and CX			TIMESTAMP(				DATE_ADD(					DATE(						SUBSTRING_INDEX(							timestamp_LeadCreated, 							' ', 							-1						), 					INTERVAL 7 DAY				), 				"23:59:59"			) 		WHEN "within_2_weeks" THEN 									-- "within_2_weeks" for QM			TIMESTAMP(				DATE_ADD(					DATE(						SUBSTRING_INDEX(							timestamp_LeadCreated, 							' ', 							-1						), 					INTERVAL 14 DAY				), 				"23:59:59"			) 		WHEN "Within the next 2 weeks" THEN 								-- "Within the next 2 weeks" for CX			TIMESTAMP(				DATE_ADD(					DATE(						SUBSTRING_INDEX(							timestamp_LeadCreated, 							' ', 							-1						), 					INTERVAL 14 DAY				), 				"23:59:59"			) 		WHEN "within_month" THEN 										-- "within_month" for QM			TIMESTAMP(				DATE_ADD(					DATE(						SUBSTRING_INDEX(							timestamp_LeadCreated, 							' ', 							-1						), 					INTERVAL 30 DAY				), 				"23:59:59"			) 		WHEN "Within the next month" THEN 								-- "Within the next month" for CX			TIMESTAMP(				DATE_ADD(					DATE(						SUBSTRING_INDEX(							timestamp_LeadCreated, 							' ', 							-1						), 					INTERVAL 30 DAY				), 				"23:59:59"			) 		WHEN "within_2_months" THEN 									-- "within_2_months" for QM			TIMESTAMP(				DATE_ADD(					DATE(						SUBSTRING_INDEX(							timestamp_LeadCreated, 							' ', 							-1						), 					INTERVAL 60 DAY				), 				"23:59:59"			) 		WHEN "Within the next 2 months" THEN 							-- "Within the next 2 months" for CX			TIMESTAMP(				DATE_ADD(					DATE(						SUBSTRING_INDEX(							timestamp_LeadCreated, 							' ', 							-1						), 					INTERVAL 60 DAY				), 				"23:59:59"			) 		WHEN "within_3_months" THEN 									-- "within_3_months" for QM			TIMESTAMP(				DATE_ADD(					DATE(						SUBSTRING_INDEX(							timestamp_LeadCreated, 							' ', 							-1						), 					INTERVAL 90 DAY				), 				"23:59:59"			) 		WHEN "Within the next 3 months" THEN 							-- "Within the next 3 months" for CX			TIMESTAMP(				DATE_ADD(					DATE(						SUBSTRING_INDEX(							timestamp_LeadCreated, 							' ', 							-1						), 					INTERVAL 90 DAY				), 				"23:59:59"			) 		WHEN "not_within_3_months" THEN 								-- "not_within_3_months" for QM			TIMESTAMP(				DATE_ADD(					DATE(						SUBSTRING_INDEX(							timestamp_LeadCreated, 							' ', 							-1						), 					INTERVAL 120 DAY				), 				"23:59:59"			) 		WHEN "Not within the next 3 months" THEN 							-- "Not within the next 3 months" for CX			TIMESTAMP(				DATE_ADD(					DATE(						SUBSTRING_INDEX(							timestamp_LeadCreated, 							' ', 							-1						), 					INTERVAL 120 DAY				), 				"23:59:59"			) 		ELSE 														-- timestamp with empty date = error			TIMESTAMP("0000-00-00 23:59:59") 	END;

Link to comment
Share on other sites

why would i need to use INNER JOIN though, its only updating a new field (timestamp_ExpectedBy) from another two fields (text_LeadAttributes and timestamp_LeadCreated) in the same table?

Link to comment
Share on other sites

ok the query has been updated again:

UPDATE 	6_serviceleads SET 	timestamp_ExpectedBy = 	CASE 		IF(			INSTR(													-- if QuoteMe (QM) else CanopyXchange (CX)				text_LeadAttributes, 				"canopy_req"			) > -1, 			SUBSTRING_INDEX(										-- get fitment attribute value for QM				SUBSTRING_INDEX(					SUBSTRING_INDEX(						6_serviceleads.text_LeadAttributes, 						'<br />', 						4					), 					';', 					-1				), 				' = ', 				-1			), 			SUBSTRING_INDEX(										-- get Fitment attribute value for CX				SUBSTRING_INDEX(					SUBSTRING_INDEX(						6_serviceleads.text_LeadAttributes, 						'<br />', 						6					), 					';', 					-1				), 				' = ', 				-1			)		) 		WHEN "ASAP" THEN 											-- "ASAP" for both QM and CX			TIMESTAMP(				SUBSTRING_INDEX(					timestamp_LeadCreated, 					' ', 					-1				) + 				INTERVAL 7 DAY, 				"23:59:59"			) 		WHEN "within_2_weeks" THEN 									-- "within_2_weeks" for QM			TIMESTAMP(				SUBSTRING_INDEX(					timestamp_LeadCreated, 					' ', 					-1				) + 				INTERVAL 14 DAY, 				"23:59:59"			) 		WHEN "Within the next 2 weeks" THEN 						-- "Within the next 2 weeks" for CX			TIMESTAMP(				SUBSTRING_INDEX(					timestamp_LeadCreated, 					' ', 					-1				) + 				INTERVAL 14 DAY, 				"23:59:59"			) 		WHEN "within_month" THEN 									-- "within_month" for QM			TIMESTAMP(				SUBSTRING_INDEX(					timestamp_LeadCreated, 					' ', 					-1				) + 				INTERVAL 30 DAY, 				"23:59:59"			) 		WHEN "Within the next month" THEN 							-- "Within the next month" for CX			TIMESTAMP(				SUBSTRING_INDEX(					timestamp_LeadCreated, 					' ', 					-1				) + 				INTERVAL 30 DAY, 				"23:59:59"			) 		WHEN "within_2_months" THEN 								-- "within_2_months" for QM			TIMESTAMP(				SUBSTRING_INDEX(					timestamp_LeadCreated, 					' ', 					-1				) + 				INTERVAL 60 DAY, 				"23:59:59"			) 		WHEN "Within the next 2 months" THEN 						-- "Within the next 2 months" for CX			TIMESTAMP(				SUBSTRING_INDEX(					timestamp_LeadCreated, 					' ', 					-1				) + 				INTERVAL 60 DAY, 				"23:59:59"			) 		WHEN "within_3_months" THEN 								-- "within_3_months" for QM			TIMESTAMP(				SUBSTRING_INDEX(					timestamp_LeadCreated, 					' ', 					-1				) + 				INTERVAL 90 DAY, 				"23:59:59"			) 		WHEN "Within the next 3 months" THEN 						-- "Within the next 3 months" for CX			TIMESTAMP(				SUBSTRING_INDEX(					timestamp_LeadCreated, 					' ', 					-1				) + 				INTERVAL 90 DAY, 				"23:59:59"			) 		WHEN "not_within_3_months" THEN 							-- "not_within_3_months" for QM			TIMESTAMP(				SUBSTRING_INDEX(					timestamp_LeadCreated, 					' ', 					-1				) + 				INTERVAL 180 DAY, 				"23:59:59"			) 		WHEN "Not within the next 3 months" THEN 					-- "Not within the next 3 months" for CX			TIMESTAMP(				SUBSTRING_INDEX(					timestamp_LeadCreated, 					' ', 					-1				) + 				INTERVAL 180 DAY, 				"23:59:59"			) 		ELSE 														-- timestamp with empty date = error			TIMESTAMP("0000-00-00 23:59:59") 	END;SELECT timestamp_LeadCreated, text_LeadAttributes, timestamp_ExpectedBy FROM 6_serviceleads ORDER BY timestamp_LeadCreated DESC LIMIT 0, 4817;

and something is most definately wrong in the way i add days to the date from the leadcreated timestamp:

			TIMESTAMP(				SUBSTRING_INDEX(					timestamp_LeadCreated, 					' ', 					-1				) + 				INTERVAL 7 DAY, 				"23:59:59"			)

how would be a better way to do it? adding 7, 14, 30, 60, 90 or 180 days to the date? and setting the time to 23:59:59 for a new timestamp.

Link to comment
Share on other sites

ok the leadattributes field differs as follows from the two sources:QuoteME

SELECT 	CASE 		IF(			INSTR(							-- if QuoteMe (QM) else CanopyXchange (CX)				text_LeadAttributes, 				"canopy_req"			) > -1, 			TRIM(				SUBSTRING_INDEX(				-- get fitment attribute value for QM					SUBSTRING_INDEX(						text_LeadAttributes, 						"<br />", 						4					), 					" = ", 					-1				)			), 			TRIM(				SUBSTRING_INDEX(				-- get Fitment attribute value for CX					SUBSTRING_INDEX(						text_LeadAttributes, 						"<br />", 						-1					), 					" = ", 					-1				)			)		) 		WHEN "ASAP" THEN 						-- "ASAP" for both QM and CX			TIMESTAMP(				DATE_ADD(					SUBSTRING_INDEX(						timestamp_LeadCreated, 						" ", 						1					), 					INTERVAL 7 DAY				), 				"23:59:59"			) 		WHEN "within_2_weeks" THEN 					-- "within_2_weeks" for QM			TIMESTAMP(				DATE_ADD(					SUBSTRING_INDEX(						timestamp_LeadCreated, 						" ", 						1					), 					INTERVAL 14 DAY				), 				"23:59:59"			) 		WHEN "Within the next 2 weeks" THEN 				-- "Within the next 2 weeks" for CX			TIMESTAMP(				DATE_ADD(					SUBSTRING_INDEX(						timestamp_LeadCreated, 						" ", 						1					), 					INTERVAL 14 DAY				), 				"23:59:59"			) 		WHEN "within_month" THEN 					-- "within_month" for QM			TIMESTAMP(				DATE_ADD(					SUBSTRING_INDEX(						timestamp_LeadCreated, 						" ", 						1					), 					INTERVAL 30 DAY				), 				"23:59:59"			) 		WHEN "Within the next month" THEN 				-- "Within the next month" for CX			TIMESTAMP(				DATE_ADD(					SUBSTRING_INDEX(						timestamp_LeadCreated, 						" ", 						1					), 					INTERVAL 30 DAY				), 				"23:59:59"			) 		WHEN "within_2_months" THEN 					-- "within_2_months" for QM			TIMESTAMP(				DATE_ADD(					SUBSTRING_INDEX(						timestamp_LeadCreated, 						" ", 						1					), 					INTERVAL 60 DAY				), 				"23:59:59"			) 		WHEN "Within the next 2 months" THEN 				-- "Within the next 2 months" for CX			TIMESTAMP(				DATE_ADD(					SUBSTRING_INDEX(						timestamp_LeadCreated, 						" ", 						1					), 					INTERVAL 60 DAY				), 				"23:59:59"			) 		WHEN "within_3_months" THEN 					-- "within_3_months" for QM			TIMESTAMP(				DATE_ADD(					SUBSTRING_INDEX(						timestamp_LeadCreated, 						" ", 						1					), 					INTERVAL 90 DAY				), 				"23:59:59"			) 		WHEN "Within the next 3 months" THEN 				-- "Within the next 3 months" for CX			TIMESTAMP(				DATE_ADD(					SUBSTRING_INDEX(						timestamp_LeadCreated, 						" ", 						1					), 					INTERVAL 90 DAY				), 				"23:59:59"			) 		WHEN "not_within_3_months" THEN 				-- "not_within_3_months" for QM			TIMESTAMP(				DATE_ADD(					SUBSTRING_INDEX(						timestamp_LeadCreated, 						" ", 						1					), 					INTERVAL 120 DAY				), 				"23:59:59"			) 		WHEN "Not within the next 3 months" THEN 			-- "Not within the next 3 months" for CX			TIMESTAMP(				DATE_ADD(					SUBSTRING_INDEX(						timestamp_LeadCreated, 						" ", 						1					), 					INTERVAL 120 DAY				), 				"23:59:59"			) 		ELSE 								-- "0000-00-00 23:59:59" unmatched error			TIMESTAMP(				"0000-00-00 23:59:59"			) 	END AS timestamp_ExpectedOn, 	text_LeadAttributes, 	timestamp_LeadCreated FROM 6_serviceleads ORDER BY timestamp_LeadCreated DESC LIMIT 0, 4817;

i've modified this to exactly use the delimiters specified in the database, but i don't get a case match for the CX leads :) i beieve there is something wrong with my case VALUE statement (currently using an if function). could somebody please help?

Edited by Pierre 'Greywacke' du Toit
Link to comment
Share on other sites

okay, i've found some records don't have all the attributes. a few are for both QM and have the fitment attribute located as the 3rd attribute, while some have extra attributes. 0o going to work a bit more at that if statement, also realised that my main problem was the if statement checking for larger than -1 - it should have been > 0. mysql INSTR functions calculate positions from 1 and no position from 0. haris s, hehe i know a normal update statement - but this is hardly normal - i post here so others may read and become more familiar with advanced mysql queries. :)with 4817 records, this query runs in 0.107 seconds and returns only 34 records with 0000-00-00 TIME as a timestamp_ExpectedBy value. will discuss with my client wether he wants the records with extra attributes at the end to have those attributes removed or not. i could do this in PHP easily, but getting named attributes in mysql seems rather hard to do.unless of course there is a way to detect how manieth SUBSTRING_INDEX is the fitment or Fitment attribute - that would be the key to the ultimate solution.the update and select errors queries:

UPDATE 	6_serviceleads SET 	timestamp_ExpectedBy = 	CASE 		IF(			INSTR(							-- if QuoteMe (QM) else CanopyXchange (CX)				text_LeadAttributes, 				"canopy_req"			) > 0, 			SUBSTRING_INDEX(					-- get fitment attribute value for QM				SUBSTRING_INDEX(					text_LeadAttributes, 					"<br />", 					4				), 				" = ", 				-1			), 			SUBSTRING_INDEX(					-- get Fitment attribute value for CX				SUBSTRING_INDEX(					text_LeadAttributes, 					"<br />", 					-1				), 				" = ", 				-1			)		) 		WHEN "ASAP" THEN 						-- "ASAP" for both QM and CX			TIMESTAMP(				DATE_ADD(					SUBSTRING_INDEX(						timestamp_LeadCreated, 						" ", 						1					), 					INTERVAL 7 DAY				), 				"23:59:59"			) 		WHEN "within_2_weeks" THEN 					-- "within_2_weeks" for QM			TIMESTAMP(				DATE_ADD(					SUBSTRING_INDEX(						timestamp_LeadCreated, 						" ", 						1					), 					INTERVAL 14 DAY				), 				"23:59:59"			) 		WHEN "Within the next 2 weeks" THEN 				-- "Within the next 2 weeks" for CX			TIMESTAMP(				DATE_ADD(					SUBSTRING_INDEX(						timestamp_LeadCreated, 						" ", 						1					), 					INTERVAL 14 DAY				), 				"23:59:59"			) 		WHEN "within_month" THEN 					-- "within_month" for QM			TIMESTAMP(				DATE_ADD(					SUBSTRING_INDEX(						timestamp_LeadCreated, 						" ", 						1					), 					INTERVAL 30 DAY				), 				"23:59:59"			) 		WHEN "Within the next month" THEN 				-- "Within the next month" for CX			TIMESTAMP(				DATE_ADD(					SUBSTRING_INDEX(						timestamp_LeadCreated, 						" ", 						1					), 					INTERVAL 30 DAY				), 				"23:59:59"			) 		WHEN "within_2_months" THEN 					-- "within_2_months" for QM			TIMESTAMP(				DATE_ADD(					SUBSTRING_INDEX(						timestamp_LeadCreated, 						" ", 						1					), 					INTERVAL 60 DAY				), 				"23:59:59"			) 		WHEN "Within the next 2 months" THEN 				-- "Within the next 2 months" for CX			TIMESTAMP(				DATE_ADD(					SUBSTRING_INDEX(						timestamp_LeadCreated, 						" ", 						1					), 					INTERVAL 60 DAY				), 				"23:59:59"			) 		WHEN "within_3_months" THEN 					-- "within_3_months" for QM			TIMESTAMP(				DATE_ADD(					SUBSTRING_INDEX(						timestamp_LeadCreated, 						" ", 						1					), 					INTERVAL 90 DAY				), 				"23:59:59"			) 		WHEN "Within the next 3 months" THEN 				-- "Within the next 3 months" for CX			TIMESTAMP(				DATE_ADD(					SUBSTRING_INDEX(						timestamp_LeadCreated, 						" ", 						1					), 					INTERVAL 90 DAY				), 				"23:59:59"			) 		WHEN "not_within_3_months" THEN 				-- "not_within_3_months" for QM			TIMESTAMP(				DATE_ADD(					SUBSTRING_INDEX(						timestamp_LeadCreated, 						" ", 						1					), 					INTERVAL 120 DAY				), 				"23:59:59"			) 		WHEN "Not within the next 3 months" THEN 			-- "Not within the next 3 months" for CX			TIMESTAMP(				DATE_ADD(					SUBSTRING_INDEX(						timestamp_LeadCreated, 						" ", 						1					), 					INTERVAL 120 DAY				), 				"23:59:59"			) 		ELSE 								-- "0000-00-00 23:59:59" unmatched error			TIMESTAMP(				"0000-00-00 23:59:59"			) 	END;SELECT 	timestamp_LeadCreated, 	text_LeadAttributes, 	timestamp_ExpectedBy FROM 6_serviceleads  WHERE timestamp_ExpectedBy < "2000-01-01 00:00:00" ORDER BY timestamp_LeadCreated DESC LIMIT 0, 34;

Edited by Pierre 'Greywacke' du Toit
Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...