Peter Pahn Peter Pahn - 5 months ago 34
MySQL Question

how to UPDATE another table ON DUPLICATE KEY with ID where DUPLICATE KEY was found

i try to create an trigger in phpmyadmin.

I will insert some values from a php formular into an mysql table..
but bevor the customer data will insert, i want to check by a trigger, does the customer data already exists. if it exists, i want to value the id, and update the id in another table. If the customer does not exist it should save the customers data.
I tryed this code but it doesent work

IF EXISTS
(SELECT * FROM `custmr` WHERE `firstname` = NEW.firstname AND `lastname` = NEW.lastname)
UPDATE `repair` SET cust_id = (SELECT id FROM custmr WHERE firstname = NEW.firstname and lastname = NEW.lastname)
ELSE
INSERT INTO `custmr` (genre, firstname, lastname, street, zip, city, phone, Mobil, mail ) VALUES (NEW.genre, NEW.firstname, NEW.lastname, NEW.street, NEW.zip, NEW.city, NEW.phone, NEW.Mobil, NEW.mail )


thanks in advance for helping

Answer

create a unique index on first + lastname

ALTER TABLE `custmr` ADD UNIQUE KEY idx_first_last (firstname, lastname);

then you can always use insert your data and if you have a duplicate key they will update the row.

sample usage of ON DUPLICATE KEY

INSERT INTO table (a,b,c) VALUES (1,2,3),(4,5,6)
    ON DUPLICATE KEY UPDATE c=VALUES(a)+VALUES(b);
Comments