CODEWITHSUNDEEP
mysqltriggers
Kburgen9
Kburgen9

Reputation: 25

MySQL: Trigger Update with random value from another tables column

Is it possible to create a trigger that updates a column with a random value from another tables column anytime that a value for that row changes.

In the attached image I have two tables, list and users. I would like for users.random to update with a new random value from the list.fruit column anytime first_name or last_name in the users table gets updated / changes.

If sam smith gets changed to andrew smith then I want pear to be updated to another random fruit from the fruits list.

This example has been obfuscated, but should explain what I’m trying to achieve.

tables

Upvotes: 0

Views: 217

Answers (1)

Akina
Akina

Reputation: 42612

CREATE TRIGGER trigger_test
BEFORE UPDATE
ON users 
FOR EACH ROW
SET NEW.random = ( SELECT fruits
                   FROM list
                   ORDER BY RAND() LIMIT 1);

https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=7c2b825177893766c054676f05f8e3ca

Upvotes: 1

Related Questions