CODEWITHSUNDEEP
mysqlsqlmariadb
Coder88
Coder88

Reputation: 1055

How to update based on not existence in another table in mysql?

This is the database server I use:

Server type: MariaDB

Server connection: SSL is not being used

Server version: 10.5.8-MariaDB - MariaDB Server

Protocol version: 10

I have a table of Users, one of the values is "isGroupLeader".

Anotther table is Groups, where the Groups have leaders.

One User can be lader of multiple groups.

When I change the leader, I want to check if the old leader is still leader on another group or not. If he's not a leader in another group, then I'll set his isGroupLeader to false.

Also in pseudocode:

if (TheUser.userID is NOT in (groupLeader column of Groups table)) {
   TheUser.isGroupLeader = false;
}

How do I do it in MySQL query MariaDB?

Upvotes: 0

Views: 53

Answers (1)

Nick
Nick

Reputation: 147256

You can maintain the state of the isGroupLeader column in the users table using triggers on INSERT, UPDATE and DELETE into groups. On an insert, set the isGroupLeader flag of the groupLeader to true. On update, you need to set the flag of the new groupLeader value, and possibly clear (if they are not group leader of another group) the flag of the old groupLeader value. On delete, clear the user's isGroupLeader flag if they are no longer a leader of any group. Note that the UPDATE and DELETE triggers must run after the UPDATE/DELETE to ensure that the EXISTS clause doesn't match the row which is being updated or deleted.

CREATE TRIGGER addLeader AFTER INSERT ON groups
FOR EACH ROW
BEGIN
  UPDATE users 
  SET isGroupLeader = true
  WHERE userID = NEW.groupLeader;
END

CREATE TRIGGER updateLeader AFTER UPDATE ON groups
FOR EACH ROW
BEGIN
  UPDATE users 
  SET isGroupLeader = userID = NEW.groupLeader
                   OR EXISTS (SELECT * FROM groups WHERE groupLeader = OLD.groupLeader)
  WHERE userID IN(OLD.groupLeader, NEW.groupLeader);
END

CREATE TRIGGER deleteLeader AFTER DELETE ON groups
FOR EACH ROW
BEGIN
  UPDATE users 
  SET isGroupLeader = EXISTS (SELECT * FROM groups WHERE groupLeader = OLD.groupLeader)
  WHERE userID = OLD.groupLeader;
END

Demo on dbfiddle

Upvotes: 1

Related Questions