0

I want to update a column in MySQL only if the row is updated. (meaning all the other values trigger mysql to update the row.)

It's a concatenated text-field, so automatic calculation using ON UPDATE in the table definition won't work. A trigger won't work either since the value is not fixed.

If there is ON UPDATE in table definition or triggers, there must be a way to determine that in some expression, right?

(Of course, I could create/update a trigger every time after the update or do a second update based on automatic update timestamp (which I then need to select first...) but that's both not very effective nor elegant.)

What I'd love to do would be something like this:

UPDATE tbl
SET x = 1, y = 2
ON UPDATE ( z = CONCAT_WS(', ',z,'blah') );
nïkö
  • 89
  • 2
  • 10
  • You can either try the cron job OR add the constraints to your column – Harisudhan. A Apr 10 '19 at 06:32
  • Have you looked at generated columns https://dev.mysql.com/doc/refman/5.7/en/create-table-generated-columns.html – P.Salmon Apr 10 '19 at 06:41
  • @Harisudhan.A sorry, i dont understand what you mean - example? – nïkö Apr 10 '19 at 07:07
  • @P.Salmon i think, that wont work, since i still cannot pass the value to be added to z without initially updating some other column with that value, right? ('blah' should only be added to z if the row is updated, meaning only if there's some change to the other columns) – nïkö Apr 10 '19 at 07:08
  • I think the only way mysql can recognise that a row has changed is if you have a timestamp column with auto_initialization and updating https://dev.mysql.com/doc/refman/8.0/en/timestamp-initialization.html. A trigger would normally be the way to do what you want but it that's not an option you might be able run a second update based on the changed timestamp. – P.Salmon Apr 10 '19 at 07:34

0 Answers0