不显示两个更新查询的记录总数 [英] not showing total number of records for two update queries

问题描述

我试图在mysql中使用两个更新语句作为存储过程

它只显示上次更新查询的受影响行数

i需要它合并

i am trying to use two update statements in mysql as stored procedure
it only shows the number of affected rows for last update query
i need it combined

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 ;

推荐答案

您要做的是在每个语句后使用 @@ ROWCOUNT 变量并将该数字放入变量中。然后从存储过程中输出该变量。这个Stack Overflow问题会有所帮助：



http://stackoverflow.com/questions/1103260/return-number-of-rows-affected-by-update-statements [ ^ ]



编辑

抱歉，我把这个问题误读为SQL。对于MySQL，概念是相同的，但您使用 ROW_COUNT（）构造而不是 @@ ROWCOUNT （at至少对于你正在做的修改语句而言。如果要从SELECT捕获行，请使用 FOUND_ROWS（）构造。

What you want to do is use the @@ROWCOUNT variable after each statement and put that number into a variable. Then output that variable from your stored procedure. This Stack Overflow question will help out a bit:

http://stackoverflow.com/questions/1103260/return-number-of-rows-affected-by-update-statements[^]

Edit
Sorry, I misread the question as being SQL. For MySQL, the concept is the same, but you use the ROW_COUNT() construct instead of @@ROWCOUNT (at least for modification statements like you are doing). If you wanted to capture rows from a SELECT, use the FOUND_ROWS() construct.

这篇关于不显示两个更新查询的记录总数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！