MySQL error when updating a full (foreign key) column based on joins with another column

I’ve been trying to create a relational database from dataframes, representing painters, paintings, institutions, so on. I have a table of paintings (combined from different datasets), that I want to connect the painter table using a 1:N foreign key, named artist_artistId. I initialize the combined paintings table with null values, I then load values from two datasets but not changing anything in the artist_artistId column.

The problem I’m facing is when I try to update the foreign key based on a condition, MySQL just gives up due to some issue and says "lost connection".
(I don’t think there is a MySQL configuration issue, I set foreign key checks to 0 before running the value update.)

Details:

The definition of the artist_artistId column:

  artist_artistId INT NULL DEFAULT NULL,
  INDEX fk_combinedpaintings_artist_idx (artist_artistId ASC) VISIBLE,
  CONSTRAINT fk_combinedpaintings_artist
    FOREIGN KEY (artist_artistId)
    REFERENCES painterpalette.artist (artistId)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION -- partially generated using a MySQL scheme

I need to update this foreign key based on the artist names, as that is given for both the CombinedPaintings and Artists tables. This can be NULL, not all painters are stored in the Artists. This query seems to return the right IDs (I believe in the same order):

SELECT a.artistId
FROM CombinedPaintingsCopy cp
LEFT JOIN Artist a ON cp.artistName = a.artistName

Based on this, I try to update the table:

UPDATE CombinedPaintings cp
JOIN Artist a ON cp.artistName = a.artistName
SET cp.artist_artistId = a.artistId;

This however breaks MySQL which loses connection and I have to restart everything. I assume it has some issues with matching the instances with the joined results.

I instead tried this approach:

CREATE TEMPORARY TABLE CombinedPaintingsCopy AS
SELECT * FROM CombinedPaintings; -- cannot modify the queried table, need to use a copy

UPDATE CombinedPaintings
SET artist_artistId = (
  SELECT a.artistId
    FROM CombinedPaintingsCopy cp
    LEFT JOIN Artist a ON cp.artistName = a.artistName
);
DROP TEMPORARY TABLE CombinedPaintingsCopy;

This just throws an error: Error Code: 1242. Subquery returns more than 1 row.
That’s intentional, I want to load values at once hence the query returns values for all rows, but this is not accepted.

(The length of the query is the same as the length of the table, SELECT COUNT(*) FROM CombinedPaintings; and SELECT COUNT(*) FROM CombinedPaintings cp LEFT JOIN Artist a ON cp.artistName = a.artistName; return the same number.
Duplicates were already removed from the painters prior, (SELECT artistName, COUNT(*) FROM Artist GROUP BY artistName HAVING COUNT(*) > 1); returns nothing (hence the left join returns the same amount of instances).)

What is the solution? Do I need to use stored procedures (e.g. a for loop would do the job)?

If more technical details are needed, I load the paintings from a WikiArt paintings dataset from Kaggle, and the Art500k dataset – I can provide more details.
MySQL Workbench version 8.0, MySQL Server 8.0

You need to sign in to view this answers

Related Post