MySQL error code when trying to create a function that checks data?
1
So I have two tables in my database that contain a member id. I'm trying to create a stored function that returns 0 when a member is NOT in table A but IS in table B.
Here's my code:
Delimiter //
CREATE FUNCTION DoesMemberExist ( mid char(10))
RETURNS INTEGER
READS SQL DATA
BEGIN
IF(((SELECT COUNT(*)
FROM members
WHERE member_id = mid) = 0) AND ((SELECT COUNT(*)
FROM rent_equipment_log
WHERE member_id = mid) > 0)) THEN
RETURN 0;
ELSE RETURN 1;
END IF;
END;
The error I'm getting is 1064 and it's saying there's a problem with my syntax but I can't figure out whats wrong? Thanks in advance!
mysql stored-functions
asked Nov 20 '18 at 17:20
rookiecookierookiecookie
105
add a comment |
1
So I have two tables in my database that contain a member id. I'm trying to create a stored function that returns 0 when a member is NOT in table A but IS in table B.
Here's my code:
Delimiter //
CREATE FUNCTION DoesMemberExist ( mid char(10))
RETURNS INTEGER
READS SQL DATA
BEGIN
IF(((SELECT COUNT(*)
FROM members
WHERE member_id = mid) = 0) AND ((SELECT COUNT(*)
FROM rent_equipment_log
WHERE member_id = mid) > 0)) THEN
RETURN 0;
ELSE RETURN 1;
END IF;
END;
The error I'm getting is 1064 and it's saying there's a problem with my syntax but I can't figure out whats wrong? Thanks in advance!
mysql stored-functions
asked Nov 20 '18 at 17:20
rookiecookierookiecookie
105
add a comment |
1
1
1
So I have two tables in my database that contain a member id. I'm trying to create a stored function that returns 0 when a member is NOT in table A but IS in table B.
Here's my code:
Delimiter //
CREATE FUNCTION DoesMemberExist ( mid char(10))
RETURNS INTEGER
READS SQL DATA
BEGIN
IF(((SELECT COUNT(*)
FROM members
WHERE member_id = mid) = 0) AND ((SELECT COUNT(*)
FROM rent_equipment_log
WHERE member_id = mid) > 0)) THEN
RETURN 0;
ELSE RETURN 1;
END IF;
END;
The error I'm getting is 1064 and it's saying there's a problem with my syntax but I can't figure out whats wrong? Thanks in advance!
mysql stored-functions
asked Nov 20 '18 at 17:20
rookiecookierookiecookie
105
So I have two tables in my database that contain a member id. I'm trying to create a stored function that returns 0 when a member is NOT in table A but IS in table B.
Here's my code:
Delimiter //
CREATE FUNCTION DoesMemberExist ( mid char(10))
RETURNS INTEGER
READS SQL DATA
BEGIN
IF(((SELECT COUNT(*)
FROM members
WHERE member_id = mid) = 0) AND ((SELECT COUNT(*)
FROM rent_equipment_log
WHERE member_id = mid) > 0)) THEN
RETURN 0;
ELSE RETURN 1;
END IF;
END;
The error I'm getting is 1064 and it's saying there's a problem with my syntax but I can't figure out whats wrong? Thanks in advance!
mysql stored-functions
mysql stored-functions
asked Nov 20 '18 at 17:20
rookiecookierookiecookie
105
asked Nov 20 '18 at 17:20
rookiecookierookiecookie
105
edited Nov 20 '18 at 17:32
rookiecookie
asked Nov 20 '18 at 17:20
rookiecookierookiecookie
105
asked Nov 20 '18 at 17:20
rookiecookierookiecookie
105
asked Nov 20 '18 at 17:20
rookiecookierookiecookie
105
105
add a comment |
add a comment |
1 Answer
1
active
oldest
votes
0
You can utilize EXISTS() instead. Also, END IF was missing to end the IF clause.
DELIMITER //
CREATE FUNCTION DoesMemberExist ( mid char(10))
RETURNS INTEGER
READS SQL DATA
BEGIN
IF( NOT EXISTS(SELECT 1 FROM members
WHERE member_id = mid)
AND
EXISTS(SELECT 1 FROM rent_equipment_log
WHERE member_id = mid) ) THEN
RETURN 0;
ELSE RETURN 1;
END IF; -- We need to END the IF clause
END //
DELIMITER ; -- Redefine the delimiter back to ;
answered Nov 20 '18 at 17:30
Madhur BhaiyaMadhur Bhaiya
19.6k62236
Thanks for that! It works great. I'm trying to call this function in a trigger and once again I am getting a syntax error. The purpose of the trigger is to delete the row in "rented_equipment_log" containing a member_id that has been removed from "members". This is the trigger: delimiter // CREATE TRIGGER DeleteMember AFTER DELETE ON members DECLARE MC INTEGER; FOR EACH ROW BEGIN SELECT DoesMemberExist(OLD.member_id) INTO mc; IF (mc = 0) THEN DELETE FROM rented_equipment_log WHERE member_id = OLD.member_id; END IF; END;//
– rookiecookie
Nov 22 '18 at 13:03
@rookiecookie It calls for a different context, hence different question :) I will be happy to assist you in a different question.
– Madhur Bhaiya
Nov 22 '18 at 13:05
I accepted it as the answer :) Unfortunately I cannot ask a question for another 2 days. Is there any other way of contacting you?
– rookiecookie
Nov 22 '18 at 13:07
@rookiecookie let me check the trigger code you sent. also specify the exact error message you are getting
– Madhur Bhaiya
Nov 22 '18 at 13:09
1
I don't have a 20 reputation so I can't type in the chat, but the code is working now. Thank you so much!
– rookiecookie
Nov 22 '18 at 14:49
|
show 7 more comments
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53398266%2fmysql-error-code-when-trying-to-create-a-function-that-checks-data%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
0
You can utilize EXISTS() instead. Also, END IF was missing to end the IF clause.
DELIMITER //
CREATE FUNCTION DoesMemberExist ( mid char(10))
RETURNS INTEGER
READS SQL DATA
BEGIN
IF( NOT EXISTS(SELECT 1 FROM members
WHERE member_id = mid)
AND
EXISTS(SELECT 1 FROM rent_equipment_log
WHERE member_id = mid) ) THEN
RETURN 0;
ELSE RETURN 1;
END IF; -- We need to END the IF clause
END //
DELIMITER ; -- Redefine the delimiter back to ;
answered Nov 20 '18 at 17:30
Madhur BhaiyaMadhur Bhaiya
19.6k62236
Thanks for that! It works great. I'm trying to call this function in a trigger and once again I am getting a syntax error. The purpose of the trigger is to delete the row in "rented_equipment_log" containing a member_id that has been removed from "members". This is the trigger: delimiter // CREATE TRIGGER DeleteMember AFTER DELETE ON members DECLARE MC INTEGER; FOR EACH ROW BEGIN SELECT DoesMemberExist(OLD.member_id) INTO mc; IF (mc = 0) THEN DELETE FROM rented_equipment_log WHERE member_id = OLD.member_id; END IF; END;//
– rookiecookie
Nov 22 '18 at 13:03
@rookiecookie It calls for a different context, hence different question :) I will be happy to assist you in a different question.
– Madhur Bhaiya
Nov 22 '18 at 13:05
I accepted it as the answer :) Unfortunately I cannot ask a question for another 2 days. Is there any other way of contacting you?
– rookiecookie
Nov 22 '18 at 13:07
@rookiecookie let me check the trigger code you sent. also specify the exact error message you are getting
– Madhur Bhaiya
Nov 22 '18 at 13:09
1
I don't have a 20 reputation so I can't type in the chat, but the code is working now. Thank you so much!
– rookiecookie
Nov 22 '18 at 14:49
|
show 7 more comments
0
You can utilize EXISTS() instead. Also, END IF was missing to end the IF clause.
DELIMITER //
CREATE FUNCTION DoesMemberExist ( mid char(10))
RETURNS INTEGER
READS SQL DATA
BEGIN
IF( NOT EXISTS(SELECT 1 FROM members
WHERE member_id = mid)
AND
EXISTS(SELECT 1 FROM rent_equipment_log
WHERE member_id = mid) ) THEN
RETURN 0;
ELSE RETURN 1;
END IF; -- We need to END the IF clause
END //
DELIMITER ; -- Redefine the delimiter back to ;
answered Nov 20 '18 at 17:30
Madhur BhaiyaMadhur Bhaiya
19.6k62236
Thanks for that! It works great. I'm trying to call this function in a trigger and once again I am getting a syntax error. The purpose of the trigger is to delete the row in "rented_equipment_log" containing a member_id that has been removed from "members". This is the trigger: delimiter // CREATE TRIGGER DeleteMember AFTER DELETE ON members DECLARE MC INTEGER; FOR EACH ROW BEGIN SELECT DoesMemberExist(OLD.member_id) INTO mc; IF (mc = 0) THEN DELETE FROM rented_equipment_log WHERE member_id = OLD.member_id; END IF; END;//
– rookiecookie
Nov 22 '18 at 13:03
@rookiecookie It calls for a different context, hence different question :) I will be happy to assist you in a different question.
– Madhur Bhaiya
Nov 22 '18 at 13:05
I accepted it as the answer :) Unfortunately I cannot ask a question for another 2 days. Is there any other way of contacting you?
– rookiecookie
Nov 22 '18 at 13:07
@rookiecookie let me check the trigger code you sent. also specify the exact error message you are getting
– Madhur Bhaiya
Nov 22 '18 at 13:09
1
I don't have a 20 reputation so I can't type in the chat, but the code is working now. Thank you so much!
– rookiecookie
Nov 22 '18 at 14:49
|
show 7 more comments
0
0
0
You can utilize EXISTS() instead. Also, END IF was missing to end the IF clause.
DELIMITER //
CREATE FUNCTION DoesMemberExist ( mid char(10))
RETURNS INTEGER
READS SQL DATA
BEGIN
IF( NOT EXISTS(SELECT 1 FROM members
WHERE member_id = mid)
AND
EXISTS(SELECT 1 FROM rent_equipment_log
WHERE member_id = mid) ) THEN
RETURN 0;
ELSE RETURN 1;
END IF; -- We need to END the IF clause
END //
DELIMITER ; -- Redefine the delimiter back to ;
answered Nov 20 '18 at 17:30
Madhur BhaiyaMadhur Bhaiya
19.6k62236
You can utilize EXISTS() instead. Also, END IF was missing to end the IF clause.
DELIMITER //
CREATE FUNCTION DoesMemberExist ( mid char(10))
RETURNS INTEGER
READS SQL DATA
BEGIN
IF( NOT EXISTS(SELECT 1 FROM members
WHERE member_id = mid)
AND
EXISTS(SELECT 1 FROM rent_equipment_log
WHERE member_id = mid) ) THEN
RETURN 0;
ELSE RETURN 1;
END IF; -- We need to END the IF clause
END //
DELIMITER ; -- Redefine the delimiter back to ;
answered Nov 20 '18 at 17:30
Madhur BhaiyaMadhur Bhaiya
19.6k62236
answered Nov 20 '18 at 17:30
Madhur BhaiyaMadhur Bhaiya
19.6k62236
answered Nov 20 '18 at 17:30
Madhur BhaiyaMadhur Bhaiya
19.6k62236
answered Nov 20 '18 at 17:30
Madhur BhaiyaMadhur Bhaiya
19.6k62236
19.6k62236
Thanks for that! It works great. I'm trying to call this function in a trigger and once again I am getting a syntax error. The purpose of the trigger is to delete the row in "rented_equipment_log" containing a member_id that has been removed from "members". This is the trigger: delimiter // CREATE TRIGGER DeleteMember AFTER DELETE ON members DECLARE MC INTEGER; FOR EACH ROW BEGIN SELECT DoesMemberExist(OLD.member_id) INTO mc; IF (mc = 0) THEN DELETE FROM rented_equipment_log WHERE member_id = OLD.member_id; END IF; END;//
– rookiecookie
Nov 22 '18 at 13:03
@rookiecookie It calls for a different context, hence different question :) I will be happy to assist you in a different question.
– Madhur Bhaiya
Nov 22 '18 at 13:05
I accepted it as the answer :) Unfortunately I cannot ask a question for another 2 days. Is there any other way of contacting you?
– rookiecookie
Nov 22 '18 at 13:07
@rookiecookie let me check the trigger code you sent. also specify the exact error message you are getting
– Madhur Bhaiya
Nov 22 '18 at 13:09
1
I don't have a 20 reputation so I can't type in the chat, but the code is working now. Thank you so much!
– rookiecookie
Nov 22 '18 at 14:49
|
show 7 more comments
Thanks for that! It works great. I'm trying to call this function in a trigger and once again I am getting a syntax error. The purpose of the trigger is to delete the row in "rented_equipment_log" containing a member_id that has been removed from "members". This is the trigger: delimiter // CREATE TRIGGER DeleteMember AFTER DELETE ON members DECLARE MC INTEGER; FOR EACH ROW BEGIN SELECT DoesMemberExist(OLD.member_id) INTO mc; IF (mc = 0) THEN DELETE FROM rented_equipment_log WHERE member_id = OLD.member_id; END IF; END;//
– rookiecookie
Nov 22 '18 at 13:03
@rookiecookie It calls for a different context, hence different question :) I will be happy to assist you in a different question.
– Madhur Bhaiya
Nov 22 '18 at 13:05
I accepted it as the answer :) Unfortunately I cannot ask a question for another 2 days. Is there any other way of contacting you?
– rookiecookie
Nov 22 '18 at 13:07
@rookiecookie let me check the trigger code you sent. also specify the exact error message you are getting
– Madhur Bhaiya
Nov 22 '18 at 13:09
1
I don't have a 20 reputation so I can't type in the chat, but the code is working now. Thank you so much!
– rookiecookie
Nov 22 '18 at 14:49
Thanks for that! It works great. I'm trying to call this function in a trigger and once again I am getting a syntax error. The purpose of the trigger is to delete the row in "rented_equipment_log" containing a member_id that has been removed from "members". This is the trigger: delimiter // CREATE TRIGGER DeleteMember AFTER DELETE ON members DECLARE MC INTEGER; FOR EACH ROW BEGIN SELECT DoesMemberExist(OLD.member_id) INTO mc; IF (mc = 0) THEN DELETE FROM rented_equipment_log WHERE member_id = OLD.member_id; END IF; END;//
– rookiecookie
Nov 22 '18 at 13:03
Thanks for that! It works great. I'm trying to call this function in a trigger and once again I am getting a syntax error. The purpose of the trigger is to delete the row in "rented_equipment_log" containing a member_id that has been removed from "members". This is the trigger: delimiter // CREATE TRIGGER DeleteMember AFTER DELETE ON members DECLARE MC INTEGER; FOR EACH ROW BEGIN SELECT DoesMemberExist(OLD.member_id) INTO mc; IF (mc = 0) THEN DELETE FROM rented_equipment_log WHERE member_id = OLD.member_id; END IF; END;//
– rookiecookie
Nov 22 '18 at 13:03
@rookiecookie It calls for a different context, hence different question :) I will be happy to assist you in a different question.
– Madhur Bhaiya
Nov 22 '18 at 13:05
@rookiecookie It calls for a different context, hence different question :) I will be happy to assist you in a different question.
– Madhur Bhaiya
Nov 22 '18 at 13:05
I accepted it as the answer :) Unfortunately I cannot ask a question for another 2 days. Is there any other way of contacting you?
– rookiecookie
Nov 22 '18 at 13:07
I accepted it as the answer :) Unfortunately I cannot ask a question for another 2 days. Is there any other way of contacting you?
– rookiecookie
Nov 22 '18 at 13:07
@rookiecookie let me check the trigger code you sent. also specify the exact error message you are getting
– Madhur Bhaiya
Nov 22 '18 at 13:09
@rookiecookie let me check the trigger code you sent. also specify the exact error message you are getting
– Madhur Bhaiya
Nov 22 '18 at 13:09
1
1
I don't have a 20 reputation so I can't type in the chat, but the code is working now. Thank you so much!
– rookiecookie
Nov 22 '18 at 14:49
I don't have a 20 reputation so I can't type in the chat, but the code is working now. Thank you so much!
– rookiecookie
Nov 22 '18 at 14:49
|
show 7 more comments
draft saved
draft discarded
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53398266%2fmysql-error-code-when-trying-to-create-a-function-that-checks-data%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
