MYSQL insert select statement

I am having trouble getting started on the developing an insert statement.

I have two tables, equipment and service.

This what I have now.

"INSERT INTO Service(Equipment_ID)
SELECT Equipment_ID FROM Equipment where department_id = 3;"

This works ok, but I need to go further.

The equipment table is an inventory of medical equipment.
all I need is the equipment id based on the department ID. Which works ok.

But I need to add additional data to the service table.  I need to add a service code( field name "code") ,  todays date (field name "problem_date", Text " safety inspection required" in field "problem" this will be the same for records in the statement.  All I get now is the equipment ID.


The purpose is to run this statement and it will generate PM workorders in the service table based on the department ID.

trying to learn sql but the requirement is needed quickly.  I am a one man show and resources are limited.
redbirdchiefAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Julian HansenCommented:
You can insert constants into the select - for instance

INSERT INTO Service(Equipment_ID,code, problem_date, problem)
SELECT Equipment_ID 
FROM Equipment, '1234', NOW(), 'The Problem is this' 
WHERE department_id = 3;"

Open in new window

Assumes you want
code='1234' for all records
problem = 'The Problem is this' for all records.
0
Julian HansenCommented:
Additional note - the problem_date field - you could probably make this a timestamp type in the database and set its default value to CURRENT_TIMESTAMP

That way you won't have to include it in the INSERT - the server will fill in the current server time at the moment the record is created.
0
Olaf DoschkeSoftware DeveloperCommented:
The lesson learned should be you can also put constants into any select list, not only fields from a queried table. That could also be variables.

I just would put it a little different to make it work:
INSERT INTO Service(Equipment_ID,code, problem_date, problem)
SELECT Equipment_ID, '1234', NOW(), 'The Problem is this' 
FROM Equipment
WHERE department_id = 3;

Open in new window

Bye, Olaf.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Julian HansenCommented:
Correction, just saw I added the fields to the wrong line.

INSERT INTO Service(Equipment_ID,code, problem_date, problem)
SELECT Equipment_ID, '1234', NOW(), 'The Problem is this'  
FROM Equipment
WHERE department_id = 3;"

Open in new window

0
redbirdchiefAuthor Commented:
Thank you both. This solution is a elegant compared to the mess I had. I tested it works fine.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
MySQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.