Greywacke Posted October 24, 2010 Share Posted October 24, 2010 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? Link to comment Share on other sites More sharing options...
Greywacke Posted October 24, 2010 Author Share Posted October 24, 2010 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 More sharing options...
[dx] Posted October 24, 2010 Share Posted October 24, 2010 Try with INNER JOIN sql function. If I'm on right side... Link to comment Share on other sites More sharing options...
Greywacke Posted October 24, 2010 Author Share Posted October 24, 2010 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 More sharing options...
Greywacke Posted October 24, 2010 Author Share Posted October 24, 2010 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 More sharing options...
Greywacke Posted October 24, 2010 Author Share Posted October 24, 2010 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? Link to comment Share on other sites More sharing options...
[dx] Posted October 24, 2010 Share Posted October 24, 2010 UPDATE table_nameSET column1=value, column2=value2,...WHERE some_column=some_value From W3schools page Link to comment Share on other sites More sharing options...
Greywacke Posted October 24, 2010 Author Share Posted October 24, 2010 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; Link to comment Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.