Stored Procedure: Update or Insert data between two databases on the same mysql server

204 Views Asked by At

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.

0

There are 0 best solutions below