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

The flddate comparison - it's matching on the highest value associated to those employees, but only one of them has that value... — OMG Ponies
– OMG Ponies, Commented Aug 6, 2010 at 4:56
tblprofile.flddate = (SELECT MAX(flddate) ... it may be returning only one date among five of them... — KoolKabin
– KoolKabin, Commented Aug 6, 2010 at 4:57
it must do the job, are you sure that select return more than one row? — Garis M Suero
– Garis M Suero, Commented Aug 6, 2010 at 4:58
I want to get the current profiles of the employee with the help of maximum date thats why i have used MAX(flddate).. No, flddate is not nullable — Bryan
– Bryan, Commented Aug 6, 2010 at 5:08

Justin King · Accepted Answer · 2010-08-06 05:14:14Z

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

edited Aug 6, 2010 at 5:14

answered Aug 6, 2010 at 5:00

Justin King

1,42812 silver badges14 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

INSERT..SELECT in MySQL

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related