Insert INTO tableA (...) Values(...) Where id from tableB

Question

基本上我有一个表的人员数据- tb_person(姓名，日期和时间，person_id等)和其他表的膳食- tb_meals(meal_id，膳食类型等)，我想在tb_meals中插入一个值，其中person_id(from tb_person)= 1

的问题是，它忽略了它只是插入每个id的条件，这是我的代码:

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

Answer 1

使用EXISTS()来测试查询其他表是否有发现

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);

演示这将添加餐1，因为person_id = 1条件为真，但不会添加餐2，因为person_id = 2为假。

Answer 2

既然person_id是tb_person上的主键，那么你可以这样做:

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

valA、valB和valC是要插入的值

为例:

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

演示