Checking if the record exist in different table

Question

I'm working on a project with the tables c_sent_messages which store sent messages from users, c_passwords which stores passwords for promotions and c_received_messages that stores the sent messages to the users from the system. I want to check if the password exists that user sent, in the c_passwords table. If exists, i want to delete the record from c_passwords table.

The code that shown below i tried. It seems ok to me but i got some errors and syntax issues i think.

DELIMITER $$
CREATE PROCEDURE flood () BEGIN
    DECLARE
        i INT DEFAULT 0;
    myloop :
    LOOP
        IF
            EXISTS ( SELECT c_passwords.pass FROM c_passwords WHERE c_passwords.pass = c_sent_messages.msg_text ) THEN-- IF c_sent_messages.stats = "N" THEN
            IF
                EXISTS ( SELECT c_sent_messages.stats FROM c_sent_messages WHERE c_sent_messages.stats = "N" ) THEN
                    INSERT INTO c_received_messages ( receiver, sender, msg_text, stats, x_date )
                VALUES
                    (
                        c_sent_messages.sender,
                        "5757",
                        "Code is received successfully.",
                    NOW());
                ELSE INSERT INTO c_received_messages ( receiver, sender, msg_text, stats, x_date )
                VALUES
                    (
                        c_sent_messages.sender,
                        "5757",
                        "Code is used before!",
                    NOW());

            END IF;
            ELSE INSERT INTO c_received_messages ( receiver, sender, msg_text, stats, x_date )
            VALUES
                (
                    c_sent_messages.sender,
                    "5757",
                    "Please send a valid code!",
                NOW());

        END IF;
        DELETE 
        FROM
            c_passwords 
        WHERE
            pass = c_sent_messages.msg_text;

        SET i = i + 1;
        IF
            i = 100 THEN
                LEAVE myloop;

        END IF;

    END LOOP myloop;

END $$DELIMITER;

When i CALL flood(); it shows the error 1054 - Unknown column 'c_sent_messages.msg_text' in 'where clause' What's wrong in my syntax? How to fix it? Or does anyone know the other way?

c_sent_messages:
id  receiver  sender  msg_text stats    x_date
1   5757    5458169867  WCQ67   N   2019-08-05 11:02:21
2   5757    5410323402  DRC73   N   2019-08-05 11:02:21
3   5757    5466491417  IEY72   N   2019-08-05 11:02:21
4   5757    5568790699  ENE72   N   2019-08-05 11:02:21

    c_passwords:
id   pass
1   AAA00
2   AAA10
3   AAA20
4   AAA30
5   AAA40

c_received_messages is now empty with the columns:
id  receiver  sender  msg_text stats    x_date

Without looking at much of your code DELETE FROM c_passwords WHERE pass = c_sent_messages.msg_tex is incorrect - you are not invoking c_sent_messages in this delete statement. Valid code would include a join to c_sent_messages. — P.Salmon
– P.Salmon, Commented Aug 6, 2019 at 6:45
BTW -- is invalid in mysql as a start of comment mysql equivalent is #, delimiter; should have a space (delimiter ;) — P.Salmon
– P.Salmon, Commented Aug 6, 2019 at 6:52
But this error is not because of the DELETE statement. It is in the IF statement. Even if i delete this rows it does not work. — miador
– miador, Commented Aug 6, 2019 at 6:58
After I fix the 2 errors mentioned in my previous comment this procedure syntaxs for me. Please add your table definitions as text to the question. — P.Salmon
– P.Salmon, Commented Aug 6, 2019 at 7:00
There is so much wrong with this code that I think you should review by breaking down into chunks and fixing a bit at a time. For starters You are not invoking c_sent_messages in your selects,inserts or delete so you will get RUN TIME errors when you call this procedure, your inserts have more fields than you are providing , you are trying to insert to a column called status but your table definition has a column called stats. AND the logic looks highly dubious to me. — P.Salmon
– P.Salmon, Commented Aug 6, 2019 at 8:32

James · Accepted Answer · 2019-08-07 10:52:47Z

0

I'v tried to recreate the procedure based on your logic. Which can be done using simple statements rather than complex.

DELIMITER $$
CREATE PROCEDURE flood () BEGIN

-- inserts data into c_received_messages when c_passwords-->pass matches with c_sent_messages -->msg_text and stats is ='N'
INSERT INTO c_received_messages (receiver, sender, msg_text, stats, x_date)
SELECT sm.sender, sm.receiver, sm.msg_text, 'Code is received successfully.', NOW()
FROM c_sent_messages sm
JOIN c_passwords p ON sm.msg_text=p.pass
WHERE sm.stats='N';

-- inserts data into c_received_messages when c_passwords-->pass matches with c_sent_messages -->msg_text and stats is <>'N'(othre than 'N')
INSERT INTO c_received_messages (receiver, sender, msg_text, stats, x_date)
SELECT sm.sender, sm.receiver, sm.msg_text, 'Code is used before!', NOW()
FROM c_sent_messages sm
JOIN c_passwords p ON sm.msg_text=p.pass
WHERE sm.stats <>'N';

-- inserts data into c_received_messages when c_passwords-->pass doesn't matches with c_sent_messages -->msg_text
INSERT INTO c_received_messages (receiver, sender, msg_text, stats, x_date)
SELECT sm.sender, sm.receiver, sm.msg_text, 'Please send a valid code!', NOW()
FROM c_sent_messages sm
LEFT JOIN c_passwords p ON sm.msg_text=p.pass
WHERE p.pass IS NULL;

-- delete the records when c_passwords-->pass matches with c_sent_messages -->msg_text
DELETE p 
FROM c_sent_messages sm
JOIN c_passwords p ON sm.msg_text=p.pass;

END $$
DELIMITER ;

answered Aug 7, 2019 at 10:52

James

1,8292 gold badges11 silver badges21 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

James Over a year ago

In which table you are talking about?

James Over a year ago

since in your code it has 5 columns but 4 values, so mention what you are missing.

miador Over a year ago

in c_received table. i fixed it by changing the places of records. But now it shows everytime "Please send a valid code!"

James Over a year ago

This shows that there is no match between pass and sent_messages table. This is what you asked?

Collectives™ on Stack Overflow

Checking if the record exist in different table

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related