CODEWITHSUNDEEP
mysql
pc_fuel
pc_fuel

Reputation: 786

Using data from one table to update columns of other table using Stored Procedures in MySQL

I am trying to write a Stored Procedure with intended action as-

When a user tries to contact me using the Contact Us Page,the details like the name,email and message are stored in the table called contactus (TABLE 1) . The Stored Procedure will check if the email id is associated with an account (based on data of another table called users (TABLE 2)). If it does, then it updates the value of account_exists as true in contactus (TABLE 1) table. Else, it sets that value to false.

I have tried some code, but I am unable to execute it due to syntax error. Also, the documentation does not clearly mention the syntax of what I want to do.

Here is what I tried

CREATE PROCEDURE `set_data_basedon_other_table`(IN em VARCHAR(500))
BEGIN
SELECT COUNT(DISTINCT email) AS noofaccounts FROM users WHERE email=em

       INSERT INTO contactus
        IF noofaccounts>0 THEN
       SET account_exists=true
       WHERE email=em
       ELSE
       SET account_exists=false
       WHERE email=em
 ENDIF      
END

Upvotes: 0

Views: 30

Answers (1)

Akina
Akina

Reputation: 42622

SP not needed, single query is enough:

UPDATE contactus
SET account_exists = EXISTS ( SELECT NULL
                              FROM users 
                              WHERE email = @em )
WHERE email = @em;

In SP form it will be

CREATE PROCEDURE `set_data_basedon_other_table`(IN em VARCHAR(500))
BEGIN
DECLARE noofaccounts BIGINT;
SELECT COUNT(DISTINCT email) 
    INTO noofaccounts 
    FROM users 
    WHERE email=em;
IF noofaccounts>0 THEN
    UPDATE contactus
        SET account_exists=true
        WHERE email=em;
ELSE
    UPDATE contactus
        SET account_exists=false
        WHERE email=em;
ENDIF;
END

Upvotes: 1

Related Questions