CODEWITHSUNDEEP
mysqlsql
xyz
xyz

Reputation: 549

Select if the record is updated using MySQL statement

I am and new to MySQL and learning it. I don't know if there is way to Select the records which are updated. For example, if I have a Student database

ID | Name |    DOB   | Total-Marks |
------------------------------------- 
01 |  ABC | 08-22-91 |   499       |
02 |  DEF | 07-14-91 |    407      |
03 |  GHI | 01-31-90 |    378      |

If there was some updates happened in the Total-Marks field, I need to select only the updated records. Can anybody tell me, how can I do this. Thanks a lot in advance.

Upvotes: 2

Views: 1967

Answers (1)

Bill Karwin
Bill Karwin

Reputation: 562230

I would add a column to the table last_updated using the TIMESTAMP data type. That data type has a special behavior that by default, it updates to the current date & time automatically when you update the row. See http://dev.mysql.com/doc/refman/5.6/en/timestamp-initialization.html for details on this.

So you would add the column:

ALTER TABLE Student ADD COLUMN last_updated TIMESTAMP 
  DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP;

Then as you INSERT/UPDATE to that table, just ignore the column. I.e. don't specify a value for the column, and the default takes over and provides the value.

Then you can query for all records updated since a given date:

SELECT * FROM Student WHERE last_updated > '2014-11-12 5:21:00';

Re your comment:

In general, you can query for rows based on values found in columns of the rows. There is no "last updated" metadata that you can query. If you cannot add a column, you can only query based on the columns you have so far.

One strategy in this case would be to query on the ID column. If you keep track somewhere else of the last ID you read, then you can query for new rows:

SELECT * FROM Student WHERE ID > $last_id_you_read

But this only finds new rows, not rows that were changed since the last time you read them. There is no way to detect this if you cannot change the table or create a trigger or another table.

Upvotes: 1

Related Questions