I am trying to create a procedure to Insert or update data into a user table in a different database on the same MariaDB server. I am using phpMyAdmin.
I have the following two tables:
Database 1: members
Table: tbl_members_contacts
ContactTypeID INT (11)
MemberID INT (11)
ContactInfo VARCHAR (255)
Indexes:
Primary Unique MemberID + ContactTypeID
ContactInfo Unique
Database 2: members_sec
Table: sec_users
login INT (11)
email VARCHAR (255)
Indexes:
Primary Unique login
I only want to update data from the table on Database 1 when the ContactTypeID = 1, which is the member's email address. If no security records exist for the member in Database 2, I need to insert a new record.
I will use an update and insert trigger to call the procedure when the data in the table in the Database are added or changed.
The Procedure which I tried to make on Database 1:
DELIMITER $$
CREATE PROCEDURE sec_usersUpdate()
BEGIN
IF tbl_members_contacts.ContactTypeID = 1 THEN
INSERT INTO members_sec.sec_users
(login, email)
VALUES
(tbl_members_contacts.MemberID, tbl_members_contacts.ContactInfo)
ON DUPLICATE KEY UPDATE
members_sec.sec_users.login = VALUES(tbl_members_contacts.MemberID),
members_sec.sec_users.email = VALUES(tbl_members_contacts.ContactInfo);
END IF;
END$$
DELIMITER ;
I receive the following message when I try to run the query on Database 1:
MySQL said: #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'END IF; END' at line 11
I have never tried to make a procedure before, so I am maybe far off. I would really appreciate some guidance.