Select updated rows in mysql

Question

Is there simple way to select updated rows?

I'm trying to store timestamp each time I am read row to be able to delete data that was not readed for a long time.

First I tried execute SELECT query first and even found little bit slow but simple solution like

 UPDATE foo AS t, (SELECT id FROM foo WHERE statement=1)q
 SET t.time=NOW() WHERE t.id=q.id

but I still want to find a normal way to do this.

I also think that updating time first and then just select updated rows should be much easier, but I didn't find anything even for this

Solution 1

Declare the time column as follows:

CREATE TABLE foo (
    ...
    time TIMESTAMP ON UPDATE CURRENT_TIMESTAMP(),
    ...)

Then whenever a row is updated, the column will be updated automatically.

UPDATE:

I don't think there's a way to update automatically during SELECT, so you have to do it in two steps:

UPDATE foo
SET time = NOW()
WHERE <conditions>;

SELECT <columns>
FROM foo
WHERE <conditions>;

As long as doesn't include the time column I think this should work. For maximum safety you'll need to use a transaction to prevent other queries from interfering.

Solution 2

For a single-row UPDATE in MySQL you could:

UPDATE foo
SET    time = NOW()
WHERE  statement = 1
AND    @var := id

@var := id is always TRUE, but it writes the value of id to the variable @var before the update. Then you can:

SELECT @var;

In PostgreSQL you could use the RETURNING clause.
Oracle also has a RETURNING clause.
SQL-Server has an OUTPUT clause.
But MySQL doesn't have anything like that.

Solution 3

@Erwin Brandstetter: Not difficult to extend the strategy of using user variables with CONCAT_WS() to get back multiple IDs. Sorry, still can't add comments...

Comments

[Barmar]

I think your title is misleading. You want to update selected rows, not select updated rows. Anyway, see my updated answer.

[George Vinokhodov]

No, it will return ~50 values. Don't really want to change database because of such a simple thing

[George Vinokhodov]

Order doesn't matter. I thought it should be a better solution then two times execute practically the same query. But you seems to be right

[Scotch]

You can define an on update in the table definition without using a trigger? Did not know this.

[Barmar]

@Scotch Only for the special case of updating a TIMESTAMP column with the current time. For anything else you have to use a trigger.

[Scotch]

Interesting, is this true with other RDBMS's?

[Scotch]

@barmar I'll look it up myself:) +1 for you though

[George Vinokhodov]

I'm not really good at sql, how can I get back ids from result string?

[fenway]

Depends on your actual UPDATE statement; but something along the lines of (re-)setting a known user variable within the query, appending each ID to the variable, and then retrieving it thereafter.