Stored procedure affecting multiple rows in MariaDB

Question

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

edit the question with some sample data and desired result would helpful. — Yogesh Sharma
– Yogesh Sharma, Commented Apr 18, 2018 at 5:52

Tim Biegeleisen · Accepted Answer · 2018-04-18 05:58:58Z

1

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 ;

edited Apr 18, 2018 at 5:58

answered Apr 18, 2018 at 5:53

Tim Biegeleisen

526k32 gold badges324 silver badges399 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Stored procedure affecting multiple rows in MariaDB

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related