Wrong player is edited

Question

I am trying to insert players into my database. If a player in a team has the same "Rugnummer" as the player that is being inserted, the player's "Rugnummer" that is being inserted should be changed. Instead the code I have know changes the "Rugnummer" of the player that already exists in the table. Running the INSERT again causes the new player to be inserted with the "Rugnummer" the already existing player had.

I have tried a couple of different things, different pieces of code and changing some things, but nothing seems to be wokring, at all, after changing some things up.

I also tried using triggers, but with an insert OLD.Rugnummer can't be used.

INSERT INTO spelers (Speler_voornaam, Speler_achternaam, Team_ID, Rugnummer)
VALUES ("Bram", "Bakker", 1, 28)

ON DUPLICATE KEY UPDATE Rugnummer = 
(select (t*10+u+1) x from (select 0 t union select 1 union select 2 union select 3 union select 4) A, 
(select 0 u union select 1 union select 2 union select 3 union select 4 union 
select 5 union select 6 union select 7 union select 8 union select 9) B 
WHERE (t*10+u+1) NOT IN (SELECT Rugnummer
FROM spelers AS s
WHERE Team_ID = 1)
ORDER BY x
LIMIT 1

The Following is an insert I tried. I expect number 1 through 10 and letter A till J. What actually is shown is A-A with the "Rugnummer" 12 and J-J is not shown. A-A is changed instead of J-J (These have the same "Rugnummer)

INSERT INTO spelers (Speler_voornaam, Speler_achternaam, Team_ID, Rugnummer)
VALUES 

("A", "A", 19, 1),
("B", "B", 19, 2),
("C", "C", 19, 3),
("D", "D", 19, 4),
("E", "E", 19, 5),
("F", "F", 19, 6),
("G", "G", 19, 7),
("H", "H", 19, 8),
("I", "I", 19, 9),
("J", "J", 19, 1)

ON DUPLICATE KEY UPDATE Rugnummer = 
(select (t*10+u+1) x from (select 0 t union select 1 union select 2 union select 3 union select 4) A, 
(select 0 u union select 1 union select 2 union select 3 union select 4 union 
select 5 union select 6 union select 7 union select 8 union select 9) B 
WHERE (t*10+u+1) NOT IN (SELECT Rugnummer
FROM spelers AS s
WHERE Team_ID = 19)
ORDER BY x
LIMIT 1)

The expected result is that the new player is inserted with a not yet used "Rugnummer" instead of needing to use the insert statement twice and changing the "Rugnummer" of the already existing player.

could you add an easy reproducible example of what you expect? — ecp, May 29 '19 at 08:16
@ecp Say I insert player "A" into team 1 with the "Rugnummer" 15. After that I would insert player "B" into team 1 as well. If I try to do so with a different "Rugnummer" it would work fine. If I try with the "Rugnummer" 15 as well the player "B" should get assigned a different "Rugnummer" (In this case the first "free" one).What is happening right now is that the "Rugnummer" of player "A" is changed, and inserting player "B" again gives him the "Rugnummer" Player "A" had (if it was a duplicate of course). — B. Bakker, May 29 '19 at 08:24
this can help you: https://stackoverflow.com/questions/2714587/mysql-on-duplicate-key-update-for-multiple-rows-insert-in-single-query — ecp, May 29 '19 at 09:29
Now everyone/thing is inserted except for the last one. How do I make sure that player J-J is added in team 19 with the "Rugnummer" 10 automatically (See edit)? — B. Bakker, May 29 '19 at 09:36
If I do that the following happens. A through I is inserted. J also has number 1. The on duplicate makes that 2 and I still get the same error but on the second spot (B-B instead of A-A). — B. Bakker, May 29 '19 at 09:52

Carlos Alves Jorge · Answer 1 · 2019-05-29T11:47:12.977

0

I think you would be better served using a function like:

    CREATE FUNCTION GetRugnummer (Rugnummer INT, Team INT) RETURNS INT
    BEGIN

      /*CODE if Rugnummer doesn't exist return Rugnummer

Otherwise search for minimum available number (see query below)*/

    END $$

Regardless of that for the query to find the minimum available number I would create a table with just one column that contains all the possible numbers.

Then you can just do a left join:

SELECT MIN(r.num) FROM RugNummers r
LEFT JOIN spelers s ON r.num=s.Rugnummer AND s.Team_ID=@Team
WHERE s.Rugnummer IS NULL;

This way in the future (in case you want to have more player numbers) you just have to add rows to the table.

If you decide to use the function then you would use it on the INSERT statement like:

INSERT INTO spelers (Speler_voornaam, Speler_achternaam, Team_ID, Rugnummer)
VALUES ("A", "A", 19, GetRugnummer(1, 19))

I think it would be much better than using DUPLICATE KEY

edited May 29 '19 at 11:47

answered May 29 '19 at 10:00

Carlos Alves Jorge

1,919
1
13
29

I tried to use your code but I get an error on the first line "You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '@Rugnummer INT, @Team INT) RETURNS INT" Am I doing something wrong or have you made an error (I am not the best in MySQL so it could very well be I am wrong myself) – B. Bakker May 29 '19 at 10:19
@B.Bakker do you need to do it in the insert or with a query later on? This would be much more easy. – ecp May 29 '19 at 10:27
@B.Bakker Sorry... Didn't see it was MySQL, it was SQL server syntax. I'm just pointing on the direction you probably should follow. You will need to make the logic for the function which would basically be a CASE WHEN / ELSE – Carlos Alves Jorge May 29 '19 at 11:48
@CarlosAlvesJorge Ah I see. I already was wondering why it wasn't working out. I already though I saw something that was more like SQL. No worries – B. Bakker May 31 '19 at 07:15
@ecp I need to do it as I insert everyone. As soon as I try to insert a player that has a used "Rugnummer" into the team they are inserted in (such as A-A AND J-J) The "Rugnummer" should automatically be changed to the first "Rugnummer" that has not been used in that team yet. Instead of J-J getting a new "Rugnummer" the number of A-A is changed, and J-J is not inserted at all. – B. Bakker Jun 03 '19 at 07:51

Wrong player is edited

1 Answers1