I'm trying to make this MySQL code more readable:
UPDATE customers
SET last_order_date = (SELECT MAX(date) FROM orders WHERE customers.customer_id = orders.customer_id),
added_note = (SELECT note FROM orders WHERE customers.customer_id = orders.customer_id
AND date = (SELECT MAX(date) FROM orders WHERE customers.customer_id = orders.customer_id));
The example code adds the date and note of the most recent order of each customer to the corresponding row in the customer table, but it has to SELECT the most recent order date for any given customer twice.
I tried the alias syntax suggested here:
UPDATE customers
SET last_order_date = (SELECT MAX(date) AS `maxdate` FROM orders WHERE customers.customer_id = orders.customer_id),
added_note = (SELECT note FROM orders WHERE customers.customer_id = orders.customer_id
AND date = `maxdate`);
I also tried without the AS keyword:
UPDATE customers
SET last_order_date = (SELECT MAX(date) maxdate FROM orders WHERE customers.customer_id = orders.customer_id),
added_note = (SELECT note FROM orders WHERE customers.customer_id = orders.customer_id
AND date = maxdate);
But in neither cases the alias is recognized.
Is there a way I can assign an intermediate result to a name and refer to it later?
With MySQL 8.0, you can use a CTE:
WITH o AS (
SELECT date, note, customer_id,
ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY date DESC) AS rownum
FROM orders
)
UPDATE customers AS c
INNER JOIN o USING (customer_id)
SET c.last_order_date = o.date,
c.added_note = o.note
WHERE o.rownum = 1;
With older versions of MySQL that don't support CTE, here's how I would code it:
UPDATE customers AS c
INNER JOIN orders AS o1
ON c.customer_id=o1.customer_id
LEFT OUTER JOIN orders AS o2
ON o1.customer_id=o2.customer_id AND o1.date < o2.date
SET c.last_order_date = o1.date,
c.added_note = o1.note
WHERE o2.customer_id IS NULL;
The outer join returns NULL for all columns of the joined table when there is no match. If there's no row o2 with a greater date than row o1, then o1 must be the row with the greatest date for the respective customer_id.
The latter solution may result in ties. That is, there may be more than one row tied for the greatest date for a given customer. The CTE solution won't have that issue.