CODEWITHSUNDEEP
phpmysql
lrpr
lrpr

Reputation: 85

How can I update the second-to-last row?

I need to update the second-to-last row in a table given a set of conditions. If it was a SELECT query it would look like this:

SELECT col_1 FROM table_1 WHERE id = id_# ORDER BY timestamp_col DESC LIMIT  1,1

However, I get an error when I do the following but it's the behavior I want:

UPDATE table_1 SET col_1 = value_# ORDER BY timestamp_col DESC LIMIT 1,1

I'm aware I can write this

UPDATE table_1 SET col_1 = value_# ORDER BY timestamp_col DESC LIMIT 1

But I can't figure out how to get the second-to-last row to update given the set of conditions.

What is the correct query to update the second-to-last row?

Upvotes: 1

Views: 89

Answers (1)

Zane Bien
Zane Bien

Reputation: 23125

MySQL does not support the "offset" feature in the LIMIT clause for UPDATE or DELETE operations.

But it is supported for SELECT statements.

So what you can do to get around this is wrap the offset LIMIT selection in a joined subselect, then update only the row where the id and timestamp_col equals the joined id and timestamp_col like so:

UPDATE table_1 a
INNER JOIN
(
    SELECT id, timestamp_col
    FROM table_1
    WHERE id = <id>
    ORDER BY timestamp_col DESC
    LIMIT 1,1
) b ON a.id = b.id AND a.timestamp_col = b.timestamp_col
SET a.col_1 = <value>

Upvotes: 2

Related Questions