displayname displayname - 1 year ago 56
MySQL Question

What is the best way to update another table based on a certain condition ON UPDATE?

I'm having two tables

. A
can be one of the following types:

public enum SubscriptionEventType {

WENT_ACTIVE, // Subscription went active and can be charged from now on.

public Long getValue() {
return this.ordinal() + 1L;

What I want to do is to keep the state of
to the most recent event. The problem: Those events do not come in correct order. E.g. it is possible to get a
event before a

So there are several way how I can accomplish what I need. First of all I can check the condition in my application layer and always set that "most recent" state based on the timestamp of the event.

Long subscriptionId = lastRecordedEvent.getSubscriptionId();

if(event.getTimestamp() > lastRecordedEvent.getTimestamp()) {
// Since the current event is more recent than all other events
// we also have to update the subscription state
subscriptionRepository.updateState(subscriptionId, event.getTimestamp());

However, I do not want to do this in my application layer. Another solution would be to use a
on the
table and let that on decide whether to update the relevant
or not. The reason why I do not go for that just yet is because I know that triggers can be easily forgotten and also be a pain to maintain. Also I know one should take every other option into account before using a
but since I am not a SQL/MySQL expert I'm not aware of all my options here.

So what would be the most practicable way to keep
up-to-date in this situation?

Answer Source

Insert your event as usual into the table and then execute the following

UPDATE subscriptions set state=events.state
FROM subscriptions inner join events on subscriptions.id = events.subscriptionID
Where events.SubscriptionId = ? and events.Timestamp = 
   (select max(timestamp) from events where events.SubscriptionId = ?)

You will need to pass parameters for the two ?s to be the subscription id of the event you just inserted


An alternative approach is rather than have a status field in the database, create a view for your subscriptions and always query the view instead.

CREATE VIEW vw_subscriptions as
  Select s.id, otherfields from subscription, coalesce(e.Status, 1) as status
  from subscriptions s left outer join events e on s.id=e.subscriptionId
  AND e.timestamp = 
   (select max(timestamp) from events where subscriptionId=s.id)

If you are worried about forgetting/maintaining the SQL or triggers, document them as comments in your repository functions and maintain all changes to the database as a change script that you store with your source code. That way your changes are all in your source control.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download