MySQL用选择插入多个记录 [英] mySQL inserting multiple records with a select
本文介绍了MySQL用选择插入多个记录的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我有一个名为car_status的字典表",其中有一个"id"列和一个"status"列.
I have a "dictionary table" called car_status that has an 'id' column and a 'status' column.
car_status
id status
1 broken
2 fixed
3 working
4 fast
5 slow
我有一个名为cars的表,其列为:id,type,status_id
I have a table called cars with the columns: id, type, status_id
cars
id type status_id
1 jeep 1
2 ford 3
3 acura 4
我希望在汽车中插入多个记录,并为它们提供与工作"相关的所有状态.最好/最简单的方法是什么?我知道我可以查询并找到正在工作"的status_id,然后执行插入查询,但是无论如何,可以使用联接或选择对一个插入查询执行此操作吗?
I am hoping to insert multiple records into cars and give them all the status associated with "working". What is the best/ easiest way? I know I can query and find the status_id that is "working", and then do an insert query, but is there anyway to do it with one insert query using a join or select?
我尝试过类似的事情:
INSERT INTO cars (type, status_id)
VALUES
('GM',status_id),
('Toyota',status_id),
('Honda',status_id)
SELECT id as status_id
FROM car_status
WHERE status = "working"
谢谢!
推荐答案
DECLARE temp_status_id INT;
SELECT status_id
INTO temp_status_id
FROM car_status;
INSERT INTO cars (type, status_id)
VALUES ('GM',temp_status_id),
('Toyota',temp_status_id),
('Honda',temp_status_id);
这篇关于MySQL用选择插入多个记录的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文