Question 194546 in Mysql Question posted in
The official MySQL documentation can be found here.

MySQL – trigger ON UPDATE without changing any other columns

Example table is defined like this:

create table testtable (
    some_id int primary key,
    ts timestamp default 0 on update CURRENT_TIMESTAMP,
    <zero or more columns...>
);

Is it possible to run UPDATE query on this table, given some_id, so that ts gets updated to the current timestamp for that row?
One way would be using

UPDATE testtable SET column1 = column1 WHERE some_id = %s

however, are there any drawbacks to this? What about when the table only has two columns – some_id and ts in this case?

Tags:

2

Answers


  1. 0 
    UPDATE testtable SET ts = CURRENT_TIMESTAMP WHERE some_id = %s;
    Login or Signup to reply.
  2. 0

    It seems like you have a table, and you want to do a sort of sneaky trick where you update a row, but you don’t actually want to change any data. Instead, you just want to make the timestamp column (ts) update itself to the current time. So, it’s like saying, "Hey database, pretend I updated something, but actually, just update the timestamp!"

    One trick you shared is telling the database to set a column to the same value it already has. Kind of like telling someone, "Change your shirt…but put on the same shirt." The database says, "Well, okay!" and goes through the motions of an update, which also updates the timestamp since it’s set to do that whenever an update happens.

    So you’d say:

    UPDATE my_table SET column_name = column_name WHERE some_id = whatever_id_you_want;

    Now, you were curious what happens if there’s no other column to "pretend update." You’d still need to fake an update to get the timestamp to refresh. So if the table only had some_id and ts, you can still do the same trick, but with some_id:

    UPDATE my_table SET some_id = some_id WHERE some_id = whatever_id_you_want;

    But, you know, even though this is a nifty trick, it’s kinda like telling a little white lie to the database, and that can have some downsides:

    Extra Effort for Database: The database still thinks something’s changed, so it’s doing extra work, even though nothing really did. This can be a bit of a drag if you have tons of data or do this trick a lot.

    Other Triggers Might Get Confused: If there are other automated actions that happen when data is updated, those will go off too! It’s like accidentally setting off an alarm.

    Unnecessary Paperwork (Logs): If your database keeps a diary (logs) of all the changes, it’s going to jot this down too, which might not be ideal since nothing really changed.

    It Locks Up the Row Temporarily: This trick puts a tiny padlock on the data while it’s "changing." If you have a bustling database with lots of activity, this might cause a traffic jam.

    So, what’s a better way? Well, if you just want to say, “Hey, update the timestamp and nothing else!” be direct and update the timestamp yourself like this:

    UPDATE my_table SET ts = CURRENT_TIMESTAMP WHERE some_id = whatever_id_you_want;

    This way, you’re honest with the database and just tell it what you really want to do. Plus, people who read your code won’t have to scratch their heads wondering why you’re doing that little trick. Clear and simple!

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search