CODEWITHSUNDEEP
mysqlsql
Driglou
Driglou

Reputation: 97

MySQL Insert values and some to be selected from other tables

I have 3 tables

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?

Upvotes: 1

Views: 122

Answers (2)

John Woo
John Woo

Reputation: 263723

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

Upvotes: 1

Barmar
Barmar

Reputation: 781004

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 ...

Upvotes: 3

Related Questions