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

Erwin Brandstetter · Answer 1 · 2013-02-22T22:47:31.817

4

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.

edited Feb 22 '13 at 22:47

answered Feb 22 '13 at 22:34

Erwin Brandstetter

605,456
145
1,078
1,228

No, it will return ~50 values. Don't really want to change database because of such a simple thing – George Vinokhodov Feb 22 '13 at 22:37

Barmar · Accepted Answer · 2013-02-22T22:35:35.913

2

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.

edited Feb 22 '13 at 22:35

answered Feb 22 '13 at 22:27

Barmar

741,623
53
500
612

I think your title is misleading. You want to update selected rows, not select updated rows. Anyway, see my updated answer. – Barmar Feb 22 '13 at 22:36
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 – George Vinokhodov Feb 22 '13 at 22:44
You can define an on update in the table definition without using a trigger? Did not know this. – Scotch Feb 23 '13 at 00:11
@Scotch Only for the special case of updating a TIMESTAMP column with the current time. For anything else you have to use a trigger. – Barmar Feb 23 '13 at 02:51
Interesting, is this true with other RDBMS's? – Scotch Feb 23 '13 at 03:20
@barmar I'll look it up myself:) +1 for you though – Scotch Feb 23 '13 at 03:21

score 1 · Answer 3 · answered Feb 23 '13 at 02:45

1

@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...

answered Feb 23 '13 at 02:45

fenway

416
2
8

I'm not really good at sql, how can I get back ids from result string? – George Vinokhodov Feb 23 '13 at 10:01
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. – fenway Feb 24 '13 at 21:01

Francesco Cattoni · Answer 4 · 2021-06-27T20:33:34.177

As suggested here you can extract the modified primary keys to update their timestamp column afterwards.

SET @uids := null;
UPDATE footable
   SET foo = 'bar'
 WHERE fooid > 5
   AND ( SELECT @uids := CONCAT_WS(',', fooid, @uids) );
SELECT @uids;
from https://gist.github.com/PieterScheffers/189cad9510d304118c33135965e9cddb

So you should replace the final SELECT @uids; with an update statement by splitting the resulting @uids value (it will be a varchar containing all the modified ids divided by ,).

Select updated rows in mysql

4 Answers4

Linked

Related