where子句中的Max（datetime_column）

I created a query to update a field. But I need to change the value of the last record. In my ps_order_history table there's the date_add column. Its type is datetime. How could I get the last record if I can't use the max() function in where clause? This is what I got until now (there's some fixed parameters, but it's just to test. I'll change this in the PHP code):

我创建了一个更新字段的查询。但我需要更改最后一条记录的值。在我的ps_order_history表中有date_add列。它的类型是datetime。如果我不能在where子句中使用max()函数,我怎么能得到最后一条记录?这是我到目前为止所得到的(有一些固定的参数,但它只是为了测试。我将在PHP代码中更改它):

UPDATE ps_order_history AS h 
INNER JOIN ps_order_detail d ON d.id_order = h.id_order
INNER JOIN ps_orders o ON o.id_order = h.id_order
SET h.id_order_state = 18
WHERE d.product_name = "Academia Mastermaq"

AND o.id_customer = (
SELECT id_customer
FROM ps_customer
WHERE firstname = "Cristiano"
AND lastname = "Ferreira dos Santos"
)

AND max(h.date_add)

Thanks.

2 个解决方案

#1

you can do that by using Order by xxx DESC LIMIT 1

你可以通过使用Order by xxx DESC LIMIT 1来做到这一点

#2

Got it! I've discovered about variables in MySQL and used a one to store the max id_order_history (I realized I didn't need the date_add column because the max id_order_history is the last data). After, I updated the table.

得到它了!我发现了MySQL中的变量,并用一个来存储最大的id_order_history(我意识到我不需要date_add列,因为max id_order_history是最后一个数据)。之后,我更新了表格。

This is the query:

这是查询:

SET @maxid = 
(
    SELECT max(h.id_order_history)

    FROM ps_order_history h

    WHERE h.id_order =
    (
        SELECT max(p.id_order)
        FROM ps_orders p
        INNER JOIN ps_order_detail d ON d.id_order = p.id_order

        WHERE p.id_customer = 
        (
            SELECT id_customer
            FROM ps_customer
            WHERE firstname = '$firstname'
            AND lastname = '$lastname'
            LIMIT 1
        )

        AND d.product_name = '$nome_produto'
        AND p.module = 'pagseguro'

        LIMIT 1
    )
);

UPDATE ps_order_history h
SET h.id_order_state = $status
WHERE h.id_order_history = @maxid;

#1