Alex Banerjee Alex Banerjee - 1 year ago 80
MySQL Question

Migrating from one table to another

I have 2 tables, one with 100,000 records of data and one with "not interested"

The table "businesses" has a "BusinessID | around 10 other info columns

the table "notinterested" has a "BusinessID" and a DateOfCall.

Currently i am filtering out the data by checking if, if exists in notinterested and the DateOfCall is less than 42 days then dont show it in the select. (code below).

SELECT * FROM businesses where PostCode like '" . $pcode . "%'
and BusinessType like '%$btype%'
and BusinessID NOT IN (SELECT BusinessID FROM telesales.notinterested WHERE NOW() BETWEEN DateOfCall and (DateOfCall + INTERVAL 42 DAY) )
and AppointmentDate is null
ORDER BY RAND() limit 100"

It was fine at first but now "notinterested" has around 30,000 records it is getting very sluggish especially with one letter postcodes.

I have added a new column to the bussinesses table called "notinterested" and i want to put a date in there instead.

What is the easyiest way to migrate the DateOfCall from the "notinterested" table into the new column in "Businesses"? Im a bit worried as its live data and dont have much time

Answer Source

To answer your original question, you can run this query to update the added date field.

UPDATE businesses
(SELECT BusinessID, MAX(DateOfCall) maxdateofcall FROM telesales.notinterested WHERE NOW() BETWEEN DateOfCall AND (DateOfCall + INTERVAL 42 DAY) GROUP BY BusinessID) recentnotinterested
    ON businesses.BusinessID = recentnotinterested.BusinessID
SET businesses.notinterested = recentnotinterested.maxdateofcall;
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download