I want to put data to employees table by inserting data in this way:
insert into employees(lastName, firstName, extension, email, officeCode, jobTitle)
select contactLastName, contactFirstName
from customers
where country like 'usa'
union all
select officeCode
from offices
where country like 'USA'
limit 1;
But extension, email and JobTitle doesn't exist in customers and offices. Is there any option to make INSERT INTO with subquery and text values for those 3 columns?
CodePudding user response:
Should be able exclude the columns you don't want as long as they aren't required fields and then you can join to your offices table.
INSERT INTO employees (lastName, firstName, officeCode, extension, email, jobTitle)
SELECT contactLastName, contactFirstName, officeCode, ‘999’, ‘[email protected]’, ‘Manager’
FROM customers
LEFT JOIN offices ON offices.country = customers.country
WHERE customers.country = 'USA'