Print/Display Subquery Count in mySQL-CodePudding

I have here a stored procedure that will update Users table based on the import tables.

CREATE PROCEDURE `UpdateUsers`()
BEGIN

DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
    SHOW ERRORS;
      ROLLBACK;
END;

START TRANSACTION;
UPDATE users SET status='A'
WHERE id IN (SELECT DISTINCT ID FROM import);
    
COMMIT;

END

How can I print/display the count of the subquery SELECT DISTINCT ID FROM import. Can I create a variable on this subquery?

CodePudding user response：

with ROWCOUNT this command tells you the number of rows that read in the database from the last query that execute. You can first execute the query (SELECT DISTINCT ID FROM import) to know, how many rows will be affected and validate if is correct

Set @@ROWCOUNT the number of rows affected or read.

here is an example.

USE AdventureWorks2012;  
GO  
UPDATE HumanResources.Employee   
SET JobTitle = N'Executive'  
WHERE NationalIDNumber = 123456789  
IF @@ROWCOUNT = 0  
PRINT 'Warning: No rows were updated';  
GO

here is more information about it.

CodePudding user response：

You can use COUNT(*) to count all the rows returned by the query or COUNT(DISTINCT ID) to get the count of all the different IDs in your query. I guess this could help you: https://www.javatpoint.com/mysql-count#:~:text=MySQL count() function is,not find any matching rows.

Anyway this would be the implementation:

CREATE PROCEDURE `UpdateUsers`()
BEGIN

DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
    SHOW ERRORS;
      ROLLBACK;
END;

START TRANSACTION;
UPDATE users SET status='A'
WHERE id IN (SELECT DISTINCT ID FROM import);
COMMIT;
SELECT COUNT(DISTINCT ID) FROM import;
END

Or in the case you want this value to be in a variable it would be like this:

DECLARE COUNT_DISTINCT INT;
SET COUNT_DISTINCT =(SELECT COUNT(DISTINCT ID) FROM import);