SEO

Here's some tables:

food
id food
1 banana
2 chicken
3 potato
4 tomato

category
id category
1 fruit
2 poultry
3 vegetable

food_category
food_id category_id
1 1
2 2
3 3
4 3

CREATE VIEW food_view AS SELECT food, category
FROM food_category fc
INNER JOIN food f ON f.id = fc.food_id
INNER JOIN category c ON c.id = fc.category_id;

Inspired by a question asked a few days ago over at forums.mysql.com,
given this schema, can a valid UPDATE query similar to that provided
below be constructed, or is a #1443 error inevitable?

UPDATE food_view SET category =
(SELECT category FROM category WHERE category.id = '1' LIMIT 1)
WHERE food = 'tomato';

-- Dump of above

CREATE TABLE `category` (
`id` tinyint(4) NOT NULL auto_increment,
`category` varchar(12) collate latin1_general_ci NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci
AUTO_INCREMENT=4 ;

INSERT INTO `category` VALUES (1, 'fruit');
INSERT INTO `category` VALUES (2, 'poultry');
INSERT INTO `category` VALUES (3, 'vegetable');

CREATE TABLE `food` (
`id` tinyint(4) NOT NULL auto_increment,
`food` varchar(12) collate latin1_general_ci NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci
AUTO_INCREMENT=5 ;

INSERT INTO `food` VALUES (1, 'banana');
INSERT INTO `food` VALUES (2, 'chicken');
INSERT INTO `food` VALUES (3, 'potato');
INSERT INTO `food` VALUES (4, 'tomato');

CREATE TABLE `food_category` (
`food_id` tinyint(4) NOT NULL,
`category_id` tinyint(4) NOT NULL,
PRIMARY KEY (`food_id`,`category_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;

INSERT INTO `food_category` VALUES (1, 1);
INSERT INTO `food_category` VALUES (2, 2);
INSERT INTO `food_category` VALUES (3, 3);
INSERT INTO `food_category` VALUES (4, 3);

CREATE VIEW food_view AS SELECT food, category
FROM food_category fc
INNER JOIN food f ON f.id = fc.food_id
INNER JOIN category c ON c.id = fc.category_id;