I need to import the old database into a new one. The old base was designed wrong. In the table there is a field called gardenId - the foreign key from the garden table. But stored in the gardenId field, the name field from the garden table.
That is, the base is not normalized and stores the name of the entity, and not the key to it. Accordingly, I need to correct this situation with the help of the request, since there is a lot of data. It is necessary in the field gardenId table to place the id from the garden table.

This is how it should be, but of course this query does not work:

 UPDATE `growing` SET gardenId = (SELECT ID FROM `garden` WHERE name = gardenId)

Tell me how to write this query correctly?

    2 answers 2

    I think the query should look like this:

      UPDATE `growing`, `garden` SET `growing`.`gardenId` = `garden`.`ID` WHERE `growing`.`gardenId` = `garden`.`name`

      You can do the following:

       UPDATE growing JOIN garden ON growing.gardenId = garden.name SET growing.gardenId = garden.gardenId