Click here to Skip to main content
15,940,921 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
i have created a stored procedure but it is not creating please find out what have i done wrong

SQL
DELIMITER //
 CREATE PROCEDURE SP_Lock_update(IN locked int(1),IN user_id int)
    BEGIN
        update user_register , comments,articles
set
user_register.locked=locked,
comments.locked=locked,
articles.locked=locked
where
user_register.user_id=user_id
and
comments.user_id=user_id
and
articles.user_id=user_id

update comments
set
locked=locked
where article_id in (select article_id from articles where user_id=user_id)
    END //
 DELIMITER ;
Posted

This is because the update statement is incorrect. You need to prefix the table name to the column when two or more tables have the same column name.
So
SQL
user_register.locked=locked,

should be
user_register.locked = [Your Table Name from where the data should be taken].locked,
 
Share this answer
 
v4
i forgot to add semi colon after the each query
SQL
DELIMITER //
 CREATE PROCEDURE SP_Lock_update(IN locked int(1),IN user_id int)
    BEGIN
        update user_register , comments,articles
set
user_register.locked=locked,
comments.locked=locked,
articles.locked=locked
where
user_register.user_id=user_id
and
comments.user_id=user_id
and
articles.user_id=user_id;

update comments
set
locked=locked
where article_id in (select article_id from articles where user_id=user_id);
    END //
 DELIMITER ;
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900