CODEWITHSUNDEEP
mysqlforeign-keys
mgtheater
mgtheater

Reputation: 31

MySQL: How to update a foreign key from the same table?

I have a table like this:

CREATE TABLE persons (
    personID int,
    name varchar(255),
    mother int,
    father int,
PRIMARY KEY (personID),
FOREIGN KEY (mother) REFERENCES persons(personID),
FOREIGN KEY (father) REFERENCES persons(personID));

I have inserted some entries with only the name and the id and would now like to run an update to connect each person with their parents, assuming that all three are already in the table.

My first guess was of course this:

UPDATE persons
SET mother = (select personID from persons where name = 'mothersname'),
    father = (select personID from persons where name = 'fathersname')
WHERE name = 'personsname';

This however results in You can't specify target table 'persons' for update in FROM clause. So I tried this:

SET @mother = (select personID from persons where name = 'mothersname');
SET @father = (select personID from persons where name = 'fathersname');
UPDATE persons
SET mother = @mother,
    father = @father
WHERE name = 'personsname';

This results in You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'set @father := (select personID from persons where name = 'fathersname');update pe' at line 1.

As usual with MySQL, this error message isn't very helpful. Can anybody give me a hint on what would be the correct syntax?

(Note that I'm running the update as a JDBC PreparedStatement, all three names get set via setString() and then processed in a batch. The exception seems to occur at the first statement.)

Upvotes: 0

Views: 81

Answers (1)

Barmar
Barmar

Reputation: 780798

Use a JOIN:

UPDATE persons AS pchild
LEFT JOIN persons as pmom ON pmom.name = 'mothersname'
LEFT JOIN persons AS pdad ON pdad.name = 'fathersname'
SET pchild.mother = pmom.personID,
    pchild.father = pdad.personID

Notice that you have to join separately for each foreign key reference.

Your second attempt, using the variable, should work. But you have to perform each query in a separate call. Most MySQL APIs don't allow you to put multiple queries in the same call.

Upvotes: 1

Related Questions