Question 20175 in Mysql Question posted in
The official MySQL documentation can be found here.

Insert INTO tableA (…) Values(…) Where id from tableB – Mysql

Basicly I have a table for persons data- tb_person (name,date&time,person_id etc) and other table for meals- tb_meals(meal_id,meal type etc)
, I want to insert a value in meal in tb_meals where person_id(from tb_person)= 1

the problem is that it is ignoring the condition it just inserting in every ids,
there is my code:

create table if not exists tb_meal(
meal_id int primary key auto_increment,
meal_type enum("Vegan","Meat","Fish")not null,
meal varchar(100) not null,
price double,
meal_date date not null,
status enum("pending","done") default("pending")
);


create table if not exists tb_person(
person_id int primary key auto_increment,
person_name varchar(100) not null,
office enum("Student","Teacher") not null,
date_order_made DATETIME default now(),
meal_ int,
FOREIGN KEY (meal_) REFERENCES tb_meal(meal_id)
);

insert into tb_pessoas(person_name,office) values("ao","student"),("Tb","Teacher");
INSERT INTO tb_almocos (id_almocos,tipo_almoco,almoco,preco,data_almoco)
SELECT 1,"Carne", "laranajaa",29.30,'2022-03-22'
FROM DUAL
WHERE EXISTS (SELECT 1 FROM tb_pessoas WHERE id_pessoas = 1);

thanks in advance

Tags:

2

Answers


  1. 0

    Use EXISTS() to test if the query of the other table finds anything.

    CREATE TABLE tb_person (
  person_id INT PRIMARY KEY,
  name VARCHAR(30)
);

CREATE TABLE tb_meals (
  meal_id INT PRIMARY KEY,
  meal_type VARCHAR(30)
);

INSERT INTO tb_person (person_id, name) VALUES (1, 'John Smith');

INSERT INTO tb_meals (meal_id, meal_type)
SELECT 1, 'Dinner'
FROM DUAL
WHERE EXISTS (SELECT 1 FROM tb_person WHERE person_id = 1);

INSERT INTO tb_meals
SELECT 2, 'Lunch'
FROM DUAL
WHERE EXISTS (SELECT 1 FROM tb_person WHERE person_id = 2);

    DEMO

    This will add meal 1 because the person_id = 1 condition is true, but will not add meal 2 because person_id = 2 is false.

    Login or Signup to reply.
  2. 0

    Since person_id is the Primary key on tb_person, then you can do it using something like this :

    INSERT INTO tb_meal (col1, col2, col3)
select valA, valB, valC
from tb_person
where person_id = 1

    valA, valB and valC are values to be inserted

    Exemple :

    INSERT INTO tb_meal (meal_id,meal_type, meal,price,meal_date) 
select 1,"Meat", "laranajaa",29.30,'2022-03-22' 
from tb_person
where person_id = 1

    Demo here

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search