Search code examples
mysqlsql-updateinner-joindistinctexists

MySQL: Update a column based on membership in a non-updateable view

I have a column in a table that I want to set to true or false based on conditions in another table that is only related to the first table via a third table. The first table is a table of deployments, the second table contains messages for each deployment (many messages per deployment), and the third table contains msg_data which only come from certain messages. Not all deployments will include the data_msg messages, but if they do I want to set a column in the deployment table to TRUE. I have created a view of the table that shows all the rows where this column should be set to true:

CREATE VIEW condition_true AS
SELECT DISTINCT deployment.id, deployment.condition
FROM deployment
JOIN messages ON messages.deployment_id = deployment.id
JOIN data_msgs ON data_msgs.messages_id = messages.id
GROUP BY deployment.id;

This view is not updateable itself for several reasons, but obviously what I'd like to do is

UPDATE condition_true
SET condition_true.condition = TRUE;

Can anyone help me figure out how to accomplish this? Thank you.

Solution

  • Assuming that id is the primary key of deployment, you need an UPDATE statement with joins of the 3 tables:

    UPDATE deployment d
JOIN messages m ON m.deployment_id = d.id
JOIN data_msgs dm ON dm.messages_id = m.id
SET d.condition = true

    Or, with EXISTS:

    UPDATE deployment d
SET d.condition = true
WHERE EXISTS (
  SELECT 1 
  FROM messages m JOIN data_msgs dm 
  ON dm.messages_id = m.id
  WHERE m.deployment_id = d.id
)

    Or, if you want to set the column to TRUE or FALSE:

    UPDATE deployment d
SET d.condition = EXISTS (
  SELECT 1 
  FROM messages m JOIN data_msgs dm 
  ON dm.messages_id = m.id
  WHERE m.deployment_id = d.id
)

    Also, in your view, there is no need for GROUP BY deployment.id, because you are not doing any aggregation and you use DISTINCT, so you will not get any duplicate rows..