UPDATE from SELECT

[benanamen]

I have an order_by_date date field that is generated based on a schedule_date. The order_by_date is the schedule_date minus days_to_order.

 

Bad data got in order_by_date so I want to update order_by_date in all rows. The following query will show me what the correct order_by_date should be. How do I take the result correct_order_by_date and update order_by_date with that value?

 

*** As I was writing this, it occurred to me a calculated date does not need to be stored in the DB, nevertheless, I would still like to know the technique to do this. See Attached image

SELECT
DATE_SUB(s.schedule_date, INTERVAL d.days_to_order DAY) AS correct_order_by_date,
l.order_by_date,
s.schedule_date,
d.days_to_order
FROM
community AS c
LEFT JOIN block AS b ON b.community_id = c.community_id
LEFT JOIN lot AS l ON l.block_id = b.block_id
LEFT JOIN `schedule` AS s ON s.lot_id = l.lot_id
LEFT JOIN days_to_order AS d ON c.days_to_order_id = d.days_to_order_id
WHERE
s.reschedule_date IS NULL AND
l.order_by_date IS NOT NULL AND
l.lot_type_id > 2 AND
s.schedule_date IS NOT NULL AND
c.community_type < 3 AND
l.active = 1 AND
l.order_by_date > s.schedule_date

Edited July 8, 2016 by benanamen

[kicken]

You can use joins in an update just like you do in a select. If you check the manual page for the UPDATE syntax you can get an idea of how it works.

 

This should work for your query:

UPDATE community AS c
LEFT JOIN block AS b ON b.community_id = c.community_id
LEFT JOIN lot AS l ON l.block_id = b.block_id
LEFT JOIN `schedule` AS s ON s.lot_id = l.lot_id
LEFT JOIN days_to_order AS d ON c.days_to_order_id = d.days_to_order_id
SET
    l.order_by_date = DATE_SUB(s.schedule_date, INTERVAL d.days_to_order DAY)
WHERE
s.reschedule_date IS NULL AND
l.order_by_date IS NOT NULL AND
l.lot_type_id > 2 AND
s.schedule_date IS NOT NULL AND
c.community_type < 3 AND
l.active = 1 AND
l.order_by_date > s.schedule_date

[benanamen]

Thanks @kicken.

 

That has me on my way. It did update many records but there are many where the datediff is not correct. I even deleted all the order_by_dates and ran the UPDATE and it still creates some dates that are not the correct difference. I even hard coded the days to subtract 21 days from schedule date. I am getting dates from -67 to 432 days difference. Very confused.

 

I broke down the update query to as simple as possible and still same result. Using:

UPDATE lot AS l
LEFT JOIN `schedule` AS s ON s.lot_id = l.lot_id
SET l.order_by_date = DATE_SUB(s.schedule_date,INTERVAL 21 DAY)

Edited July 8, 2016 by benanamen

[benanamen]

Found the problem. In the schedule table, a lot_id can be there more than once. Mysql is using the last rows schedule_date to calculate. I will have to add one more piece of criteria to use the correct row.

 

@Kicken, thanks for your help.