Can I alias a select query in a multiline update in MySQL?-CodePudding

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?

CodePudding user response：

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.