CODEWITHSUNDEEP
mysqltriggers
thanos_zach
thanos_zach

Reputation: 156

Mysql triggers passing data from one table to another matching tables

I've table1 with columns (id, firstname, lastname) and table2 with columns (id, firstname, lastname, ans3, mandate, matchid). So far i've made this trigger that inserts firstname and lastname from table1 to table2. Now the thing that i want is to join the matchid from table2 with the id from table1. For example when matchid = 1 display records from table1 with id = 1 and in the same row the table2 records. Here's what i do so far with the trigger.

DELIMITER //

CREATE TRIGGER after_user_insert AFTER INSERT ON table1
FOR EACH ROW
BEGIN

  INSERT INTO table2 (firstname, lastname, matchid)
  VALUES (NEW.firstname, NEW.lastname, NEW.id);

END; //

DELIMITER ;

Here's a photo of what i get in phpmyadmin. You can see that i didn't figure how to join table2.matchid to table1.id

You can see that i didn't figure how to join table2.matchid to table1.id

---edit--- enter image description here

Upvotes: 1

Views: 1138

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522797

From your comments it sounds like perhaps you intended to update a user record which already exists in table2. If so, you can use ON DUPLICATE KEY when you update:

DELIMITER //

CREATE TRIGGER after_user_insert AFTER INSERT ON table1
FOR EACH ROW
BEGIN

    INSERT INTO table2 (firstname, lastname, matchid)
    VALUES (NEW.firstname, NEW.lastname, NEW.id)
    ON DUPLICATE KEY UPDATE matchid = NEW.id;

END; //

DELIMITER ;

This solution would require that there is a unique index on both the first and last name in table2. If you don't have such a unique index, then you can add one:

ALTER TABLE table2 ADD UNIQUE unq_idx (firstname, lastname);

Upvotes: 2

Related Questions