INSERT..SELECT in MySQL

Question

INSERT INTO tblprofile (fldDate,fldemployeeno,fldLName,fldFName,fldMI,fldEmployeeName,
                        fldShiftCode,fldProjectGroup,fldTask,fldSuperior,fldPosition)
SELECT now(),tblprofile.fldemployeeno,tblprofile.fldlname,tblprofile.fldfname,tblprofile.fldmi,tblprofile.fldemployeename,
       tblprofile.fldshiftcode,tblprofile.fldprojectgroup,tblprofile.fldtask,tblprofile.fldsuperior,tblprofile.fldposition
  FROM tblprofile
 WHERE tblprofile.flddate = (SELECT MAX(flddate) FROM tblprofile p
                              WHERE p.fldemployeeno IN ('EMP0001','EMP0002','EMP0003','EMP0004','EMP0005'))
   AND tblprofile.fldemployeeno IN
       ('EMP0001','EMP0002','EMP0003','EMP0004','EMP0005');

I want to copy all current profile of all employees in a list ('EMP0001','EMP0002','EMP0003','EMP0004','EMP0005') and it seems that in my query it only insert one (1) rows..

In short, I want to try to copy all current profile of the employee from tblprofile in just one execution? Can anyone help me to do that so..

Answer 1

From your comments it appears that a user could have multiple profiles and you want the latest and set a current date for the new profile.

Change your WHERE clause to this

FROM tblprofile t1 WHERE t1.flddate = (SELECT MAX(flddate) FROM tblprofile p WHERE p.fldemployeeno = t1.fldemployeeno) AND t1.fldemployeeno IN ('EMP0001','EMP0002','EMP0003','EMP0004','EMP0005');