One Query Instead Of Two?

[Josee]

Hello,

 

I have three tables in my database: ingredients , categories, and an intermediary table: ingredients_categories. Let's say I inserted id_ingredient 100 to 150 into ingredients. Right now, if I want to populate the intermediary table I use two queries:

 

INSERT INTO ingredients_categories (id_ingredient)
  SELECT id_ingredient FROM ingredients WHERE id_ingredient BETWEEN 100 AND 150;
UPDATE ingredients_categories SET id_categorie=11 WHERE id_ingredient BETWEEN 100 AND 150;

 

Is there a smarter way of doing this, with just one query?

 

With thanks for your help,

[Larry]

Well, with this very specific example, you could do:

INSERT INTO ingredients_categories (id_ingredient, ingredients_categories) SELECT id_ingredient, 11 FROM ingredients WHERE id_ingredient BETWEEN 100 and 150

 

Basically you're just "selecting" a static value.

[Josee]

Thank you, Larry. I "knew" there had to be a simpler solution. The next step, of course, will be to see if I can still get it to work when I use variables instead of static values. But I didn't think you could use "foreign" values in such a case. This opens up new vistas for me.

[Larry]

Yeah, selecting static values like that is something that’s both quite obvious (in hindsight) and yet not at all intuitive. Quite useful in many situations, though!