CODEWITHSUNDEEP
sqlmariadb
BelottiGhilardi
BelottiGhilardi

Reputation: 75

Stored procedure affecting multiple rows in MariaDB

I'm running a database under the Marian engine and I have two tables:

Users(id, score)
Replies(user ID, reply)

Now I have to write a stored procedure that, given a number X, increments the field score by 1 for all the users that have replied with X.

How can I do that? I tried to do an update query, but MariaDB doesn't seem to work with the from clause.

Thanks in advance

Upvotes: 0

Views: 88

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521194

The update query itself is straightforward:

UPDATE Users u
SET score = score + 1
WHERE EXISTS (SELECT 1 FROM Replies r WHERE r.userID = u.id AND reply = 'X');

Inside a stored procedure, it might look like:

DELIMITER //
CREATE PROCEDURE addScore (reply INT)
BEGIN
SET @query = 'UPDATE Users u
    SET score = score + 1
    WHERE EXISTS (SELECT 1 FROM Replies r WHERE r.userID = u.id AND reply = ?)'

SET @x = reply;
PREPARE stmt FROM @query;
EXECUTE stmt USING @x;
DEALLOCATE PREPARE stmt;
END; //
DELIMITER ;

Upvotes: 1

Related Questions