SQL multi update

Question

Witam. Moim pragnieniem jest napisac kwerende ktora zrobi update na tabeli dla kazdej kolumny z ta sama profesja. Zgaduje ze trzeba uzyc subqueries ale jeszcze nie rozumiem ich za dobrze. Jakies pomysly?

CREATE TABLE `my_contacts` (
  `last_name` varchar(30) ,
  `first_name` varchar(20) ,
  `email` varchar(50) ,
  `gender` char(1),
  `birthday` date ,
  `profession` varchar(50),
  `location` varchar(50),
  `status` varchar(20),
  `interests` varchar(100),
  `seeking` varchar(100)
);

INSERT INTO `my_contacts` (`last_name`,`first_name`,`email`,`gender`,`birthday`, `profession`,`location`) VALUES ('Toth','Anne','Anne_Toth@leapinlimos.com','F','1969-11-18', 'Artist','San Fran, CA');
INSERT INTO `my_contacts` (`last_name`,`first_name`,`email`,`gender`,`birthday`, `profession`,`location`) VALUES ('Manson','Anne','am86@objectville.net','F','1977-08-09', 'Baker','Seattle, WA');
INSERT INTO `my_contacts` (`last_name`,`first_name`,`email`,`gender`,`birthday`, `profession`,`location`) VALUES ('Hardy','Anne','anneh@b0tt0msup.com','F','1963-04-18', 'Teacher','San Fran, CA');
INSERT INTO `my_contacts` (`last_name`,`first_name`,`email`,`gender`,`birthday`, `profession`,`location`) VALUES ('Parker','Anne','annep@starbuzzcoffee.com','F','1983-01-10', 'Student','San Fran, CA');
INSERT INTO `my_contacts` (`last_name`,`first_name`,`email`,`gender`,`birthday`, `profession`,`location`) VALUES ('Blunt','Anne','anneblunt@breakneckpizza.com','F','1959-10-09', 'Web Designer','San Fran, CA');
INSERT INTO `my_contacts` (`last_name`,`first_name`,`email`,`gender`,`birthday`, `profession`,`location`) VALUES ('Jacobs','Anne','anne99@objectville.net','F','1968-02-05', 'Computer Programmer','San Jose, CA');

CREATE TABLE profession(
profession_id INT PRIMARY KEY NOT NULL AUTO_INCREMENT,
profession VARCHAR(50)
)AS SELECT profession FROM my_contacts GROUP BY profession ORDER BY profession;

SELECT * FROM profession;

ALTER TABLE my_contacts ADD profession_id INT;
ALTER TABLE my_contacts ADD CONSTRAINT pk_profession_id_professions_fk_profession_id_my_contacts FOREIGN KEY my_contacts(profession_id) REFERENCES profession(profession_id);
UPDATE my_contacts SET my_contacts.profession_id = profession.profession_id WHERE profession(profession) = my_contacts(profession);

Ten ostatni update nie dziala.