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
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 ;