1

I doing practice with MariaDB stored procedures. I created a simple stored procedure that should update a row in a table, but it does not update it.

USE testdb;
CREATE TABLE IF NOT EXISTS games_status (player_1 VARCHAR(30), player_2 VARCHAR(30), player_3 VARCHAR(30), player_4 VARCHAR(30), player_5 VARCHAR(30), player_6 VARCHAR(30), game_room INT);
INSERT INTO games_status (game_room) VALUE (1);

DELIMITER $$
CREATE PROCEDURE new_player(in_name VARCHAR(30), in_game_room INT)
BEGIN
    DECLARE var_player_1 VARCHAR(30);
    DECLARE var_player_2 VARCHAR(30);
    DECLARE var_player_3 VARCHAR(30);
    DECLARE var_player_4 VARCHAR(30);
    DECLARE var_player_5 VARCHAR(30);
    DECLARE var_player_6 VARCHAR(30);
    
    SELECT player_1, player_2, player_3, player_4, player_5, player_6
        INTO var_player_1, var_player_2, var_player_3, var_player_4, var_player_5, var_player_6
        FROM games_status
        WHERE game_room = in_game_room;
    
    IF var_player_1 = NULL THEN
        UPDATE games_status
        SET
            player_1 = in_name
        WHERE
            game_room = in_game_room;
    END IF;
END $$
DELIMITER ;

When I run

CALL new_player("Tom", 1);
SELECT * FROM games_status;

I get

+----------+----------+----------+----------+----------+----------+-----------+
| player_1 | player_2 | player_3 | player_4 | player_5 | player_6 | game_room |
+----------+----------+----------+----------+----------+----------+-----------+
| NULL     | NULL     | NULL     | NULL     | NULL     | NULL     |         1 |
+----------+----------+----------+----------+----------+----------+-----------+

So player_1 instead of being Tom, is still NULL. Where did I make a mistake?
mariadb version is 10.4.13
Thank you

Ctrl_halt
  • 43
  • 4
  • 1
    The table is empty. You cannot update a row that does not exist. You need to initialise the table with an insert for each room that puts null into the player columns. Then you can add players to those rows. – Nikkorian Aug 13 '20 at 14:44
  • @Nikkorian `INSERT INTO games_status (game_room) VALUE (1);` should just initialise a row with a game_room value of `1` and leaving other fields empty, therefore `NULL`. Isn't that correct? – Ctrl_halt Aug 13 '20 at 14:47
  • OK another problem is that your select could have more than one row in the result because your game_room column is not declared as a unique value. With this possibility you cannot select INTO variables. My guess is you're triggering an exception in the Stored proc but you're not seeing that. What environment are you using? – Nikkorian Aug 13 '20 at 14:54
  • Answer to your comment - yes, – Nikkorian Aug 13 '20 at 14:54
  • try this DROP TABLE IF EXISTS games_status; CREATE TABLE... game_room INT **UNIQUE**); – Nikkorian Aug 13 '20 at 14:57
  • @Nikkorian `UNIQUE` did not solve the problem. The platform is mariadb version 10.4.13 – Ctrl_halt Aug 13 '20 at 15:02
  • 1
    See [MySQL comparison with null value](https://stackoverflow.com/q/9608639) (in case you miss the reference, it is in relation to your code "IF var_player_1 = NULL THEN") – Solarflare Aug 13 '20 at 15:07
  • @Solarflare Thank you, I updated the post by replacing `IF var_player_1 = NULL THEN` with `IF var_player_1 IS NULL THEN` but the problem still persists – Ctrl_halt Aug 13 '20 at 15:20
  • 1
    No, it doesn't (or rather: shouldn't) persist, see your code running in [fiddle](https://dbfiddle.uk/?rdbms=mariadb_10.4&fiddle=dce38c099c85f66e97f8add2633b02ba). If it does persist for you, you did something you didn't mention, maybe check if you actually modified the procedure (`show create procedure new_player`). Btw, you can also get the same effect as with your procedure if you (just) use `update games_status SET player_1 = in_name WHERE game_room = in_game_room AND player_1 is null` (without the `... into var_player_1 ...` and the `if`-condition). – Solarflare Aug 13 '20 at 16:12
  • @Solarflare ok it works, can you fill an answer so I can give you the green mark? – Ctrl_halt Aug 13 '20 at 20:35
  • The question has been answered in the linked answer (and there is not much else I could say), but feel free to self-answer to mark your problem as solved (but you should revert the one line of code to it's original version, otherwise, the problem would not be present). – Solarflare Aug 14 '20 at 13:29
  • @Solarflare I reverted to `IF var_player_1 = NULL THEN` so you can add your answer – Ctrl_halt Aug 14 '20 at 18:35

0 Answers0