MySQL 8.0.31: Foreign Key Value Overwritten with 'Null' when another Value is Assigned-CodePudding

I have two tables that are associated with one another through a foreign key, 'character_id' in my 'grid_location' table that references the primary key, 'character_id' in my 'character' table. I'm attempting to update the 'character_id' foreign key value for the entry with a "grid_number" primary key value of two in the 'grid_location' table with the "character.character_id" value corresponding to the character with first name of "Andrew" and last name of "Bernard" from the "character" table. When I run the following query with all default "grid_number.character_id" values assigned to "Null", the value of "grid_location.character_id" for the entry corresponding to grid location two is successfully updated as shown in the first image below:


UPDATE grid_location
SET grid_location.character_id = (
    SELECT `character`.character_id
    FROM `character`
    WHERE `character`.first_name = 'Andrew' AND `character`.last_name = 'Bernard' AND grid_location.grid_number = 2
);

However, when I attempt to execute a similar query to update the 'character_id' foreign key value for the entry with a "grid_number" primary key value of three in the 'grid_location' table with the "character.character_id" value corresponding to the character with first name of "Angela" and last name of "Martin" from the "character" table, the value is successfully set for the grid_number value of three, but the previous value set for the grid number of two is overwritten to "Null" as if only one foreign key value can take on a "Not Null" value at a time. Must I set the "grid_location.character_id" foreign key entries to a default value other than NULL, or does the issue perhaps lie in the different number of entries in the "character" and "grid_location" tables, namely 42 and 9 respectively? The query used to execute this command, the "character" table, and the resulting "grid_location" table are shown below. As an aside, I am not able to write out the contents of the tables to a file because of unresolved permissions restrictions and so I had no choice but to insert screenshots into this issue.

UPDATE grid_location
SET grid_location.character_id = (
    SELECT `character`.character_id
    FROM `character`
    WHERE `character`.first_name = 'Angela' AND `character`.last_name = 'Martin' AND grid_location.grid_number = 3
);

CodePudding user response：

Since your query does not have a WHERE clause, it always updates all of the rows in the grid_location table.

For each row, it executes the subselect, and the subselect only returns a non-null value if the grid_number of the "current" row matches the number specified after grid_location.grid_number = . It returns null for all of the other rows.

I think you're looking for this instead:

UPDATE grid_location
SET grid_location.character_id = (
    SELECT `character`.character_id
    FROM `character`
    WHERE `character`.first_name = 'Andrew' AND `character`.last_name = 'Bernard'
)
WHERE grid_location.grid_number = 2;