June 22, 2011 at 1:31 am
Hi
I am trying to do an insert into a table (employer) where I am grabbing a max date from another table which has multiple instances of the employer.
I will tie the date back to the employer table using the employer.identifier
The statement below brings me back the data I want, but I am having trouble turning it into an insert statement. I was trying to use the statement as a sub query. Can anyone point me in the right direction
Thanks
Nigel
select employer.identifier, max ( TrainingProgramme.DateTrainingTerminated )
from Employer
inner join TrainingAgreement on
ssEmployer.Tims_employerId = TrainingAgreement.employerid
inner join TrainingProgramme on
TrainingAgreement.trainingcontractId = TrainingProgramme.trainingcontractid
where Employer.EmployeesCurrentlyinTraining = 0
group by Employer.identifier
June 22, 2011 at 2:48 am
It seems to be simple enough , is there any particular issue your facing when performing the insert
Insert into employer (identifier , DateTrainingTerminated )
select employer.identifier, max ( TrainingProgramme.DateTrainingTerminated )
from Employer
inner join TrainingAgreement on
ssEmployer.Tims_employerId = TrainingAgreement.employerid
inner join TrainingProgramme on
TrainingAgreement.trainingcontractId = TrainingProgramme.trainingcontractid
where Employer.EmployeesCurrentlyinTraining = 0
group by Employer.identifier
or
with cte as (
select employer.identifier, max ( TrainingProgramme.DateTrainingTerminated )
from Employer
inner join TrainingAgreement on
ssEmployer.Tims_employerId = TrainingAgreement.employerid
inner join TrainingProgramme on
TrainingAgreement.trainingcontractId = TrainingProgramme.trainingcontractid
where Employer.EmployeesCurrentlyinTraining = 0
group by Employer.identifier
)
insert into employer (identifier , DateTrainingTerminated )
select * from cte
June 22, 2011 at 1:01 pm
Thanks for your reply, Sorry i had this a little wrong, guess that happens when you do these things last thing at night
I want to "UPDATE" a column in the employer table rather than insert
Thanks
Nigel
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply