当前位置: 动力学知识库 > 问答 > 编程问答 >

sql - MySQL Insert values and some to be selected from other tables

问题描述:

I have 3 tables

  • works (id, name, date_started, date_finished, type_id, employer_id)
  • types (id, type)
  • employers (id, name, last_name, company)

I want when inserting into works table records in one query to insert into columns: name, date (by hand), then also into type_id and employer_id columns (selecting id from other tables)

I know you can insert and then use select, but how do i add the other values, that i need to type by hand as they dont exist on other tables?

网友答案:

You can just put the extra values directly in the SELECT clause:

INSERT INTO works (name, date_started, type_id, employer_id)
SELECT name, curdate(), type_id, employer_id
FROM types t
JOIN employers e ON ...
WHERE ...
网友答案:

You can have subqueries inside VALUES() clause, provided the subquery will only return one value.

INSERT INTO works (name, date_started, date_finished, type_id, employer_id)
VALUES
(
    'name here',
    'date started',
    'date finished',
    (SELECT id FROM types WHERE type = 'val' LIMIT 1),
    (SELECT id FROM employers WHERE name = 'val' LIMIT 1)
)
分享给朋友:
您可能感兴趣的文章:
随机阅读: