Updating certain data from one table to another table once a week

Question

My end goal is to update information from table 1 to table 2 on a certain condition. More specifically, I'd like to update the date from table1 to table2 where the id's match and dates from table2 are NULL.

I want this to happen every Sunday.

Here are the specifics:

** Keep in mind, payee_id and id are the same but in two different tables. **

The table I plan to copy from is called orders, but I only want to select certain data from this table. My query looks like this:

TABLE 1

SELECT movement.payee_id,
min(origin_stop.sched_arrive_early) first_date

FROM orders

LEFT JOIN movement_order ON orders.id = movement_order.order_id
LEFT JOIN movement ON movement.id = movement_order.movement_id
LEFT JOIN stop as origin_stop ON origin_stop.id = movement.origin_stop_id

WHERE orders.status <> 'V'
GROUP BY movement.payee_id

OUTPUT 1

payee_id           |  first_date
-------------------|-----------------
STRINGID1          |  2013-12-20 15:00:00.000
STRINGID2          |  2013-12-27 13:00:00.000

TABLE 2

SELECT id, initial_date
FROM drs_payee

OUTPUT 2

id                 |  initial_date
-------------------|-----------------
STRINGID1          |  NULL
STRINGID2          |  NULL

TABLE 2 OUTPUT SHOULD BE:

id                 |  initial_date
-------------------|-----------------
STRINGID1          |  2013-12-20 15:00:00.000
STRINGID2          |  2013-12-27 13:00:00.000

My attempt to solve this:

UPDATE drs_payee a
    (SELECT movement.payee_id,
     min(origin_stop.sched_arrive_early) carrier_first_shipped_date

     FROM orders

     LEFT JOIN movement_order ON orders.id = movement_order.order_id
     LEFT JOIN movement ON movement.id = movement_order.movement_id
     LEFT JOIN stop as origin_stop ON origin_stop.id =    movement.origin_stop_id

     WHERE orders.status <> 'V'
     GROUP BY movement.payee_id) b
ON a.id = b.payee_id
SET a.initial_date = b.first_date
WHERE a.initial_date IS NULL

Not sure if the format of this is correct and if you can even do a SELECT inside an UPDATE like shown above.

I believe I have to have a loop to find id's that match (i.e. where payee_id = id and initial_date is NULL). Would this be created in a stored procedure to loop through id's and create a weekly job schedule or would some type of update query be enough? Please help or point me in some direction with the an update query or stored procedure example using my data if possible.

Thanks in advance everyone!

Answer 1

For scheduled queries the best thing for me is creating events. First you have to select your db, and at the top right you'll see an option in phpmyadmin called "events". You have to turn on the events planner, and to do that you mut be logged in with a super privilege user, in phpMyadmin.

After you do that the query is:

DROP EVENT `dates update` ;

CREATE DEFINER =  `your_user_name`@`your_host_name` 
EVENT `dates update` 
ON SCHEDULE EVERY1 WEEK STARTS '2015-07-29 03:00:00' 
ON COMPLETION NOT PRESERVE ENABLE DO 


UPDATE movement a
INNER JOIN orders b
ON a.payee_id = b.payee_id
SET a.initial_date = b.first_ship_date
WHERE a.initial_date IS NULL;

It must work.

If you need help about super privilege check this

I hope it works for you.

Caio Ortega

Answer 2

Update table2 inner join table1 on payee_id = id set initial_date=first_date where initial_date is null

Through SQL server agent we can schedule the query as per our requirement (For example every Sunday )