MySQL用选择插入多个记录 [英] mySQL inserting multiple records with a select

问题描述

我有一个名为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屋！