存储过程更新 [英] stored procedure update

问题描述

我创建了一个存储过程，但它没有创建请找出我做错了什么

i have created a stored procedure but it is not creating please find out what have i done wrong

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 ;

推荐答案

这是因为更新语句不正确。当两个或多个表具有相同的列名时，您需要在表名前加上列。

所以

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

user_register.locked=locked,

应该是

should be

user_register.locked = [Your Table Name from where the data should be taken].locked,

我忘了在每个查询后添加半冒号

i forgot to add semi colon after the each query

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 ;

这篇关于存储过程更新的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！