What is wrong with this INSERT INTO SELECT statement?

Question

I am trying to run this code

INSERT INTO payroll_ho.tb_quotapos(QPCompany,QPQRndYear,QPQRndMonth,QPLine,QPPart,QPDep,QPBand,QPSubBand,QPPos,QPExist1,QPExist2,QPExist3)
SELECT "G02",2016,6,mp.PerWorkLine,mp.PerPart,mp.PerDep,PerBand,PerSubBand,mp.PerPos,c1,c2,c3 FROM ms_per mp
 LEFT JOIN payroll_ho.tb_subbandpos ON BPBand=mp.PerSubBand
 LEFT JOIN (SELECT PerWorkLine,PerPart,PerDep,PerPos,count(*) c1 FROM ms_per WHERE PerQuota=1 AND PerEmploying="Y" AND PerISO="Y"  AND PerCGroup = "01"  GROUP BY PerWorkLine,PerPart,PerDep,PerPos) mp1 ON mp.PerWorkLine=mp1.PerWorkLine AND mp.PerPart=mp1.PerPart AND mp.perdep=mp1.perdep AND mp.perpos=mp1.perpos
 LEFT JOIN (SELECT PerWorkLine,PerPart,PerDep,PerPos,count(*) c2 FROM ms_per WHERE PerQuota=2 AND PerEmploying="Y" AND PerISO="Y"  AND PerCGroup = "01"  GROUP BY PerWorkLine,PerPart,PerDep,PerPos) mp2 ON mp.PerWorkLine=mp2.PerWorkLine AND mp.PerPart=mp2.PerPart AND mp.perdep=mp2.perdep AND mp.perpos=mp2.perpos
 LEFT JOIN (SELECT PerWorkLine,PerPart,PerDep,PerPos,count(*) c3 FROM ms_per WHERE PerQuota=3 AND PerEmploying="Y" AND PerISO="Y"  AND PerCGroup = "01"  GROUP BY PerWorkLine,PerPart,PerDep,PerPos) mp3 ON mp.PerWorkLine=mp3.PerWorkLine AND mp.PerPart=mp3.PerPart AND mp.perdep=mp3.perdep AND mp.perpos=mp3.perpos
 WHERE mp.PerEmploying="Y"
  AND (c1>0 OR c2>0 OR c3>0)
 GROUP BY mp.PerWorkLine,mp.PerPart,mp.PerDep,PerBand,PerSubBand,mp.PerPos
ON DUPLICATE KEY UPDATE QPExist1=c1, QPExist2=c2, QPExist3=c3

MySQL returned an error Unknown column 'c1' in 'field list' Why MySQL doesn't see c1 in the SELECT clause?

EDIT: I think the problem is in the ON DUPLICATE clause. The SELECT works fine.

EDIT2: Here is the definitions for each table.

CREATE TABLE `tb_quotapos` (
  `QPCompany` varchar(3) NOT NULL,
  `QPQRndYear` int(4) NOT NULL,
  `QPQRndMonth` int(2) NOT NULL,
  `QPLine` varchar(2) NOT NULL,
  `QPPart` varchar(3) NOT NULL,
  `QPDep` varchar(4) NOT NULL,
  `QPBand` varchar(2) NOT NULL,
  `QPSubBand` varchar(3) NOT NULL,
  `QPPos` varchar(4) NOT NULL,
  `QPJobDesc` varchar(15) DEFAULT NULL,
  `QPQ1` int(3) DEFAULT NULL,
  `QPQ2` int(3) DEFAULT NULL,
  `QPQ3` int(3) DEFAULT NULL,
  `QPExist1` int(3) DEFAULT NULL,
  `QPExist2` int(3) DEFAULT NULL,
  `QPExist3` int(3) DEFAULT NULL,
  PRIMARY KEY (`QPCompany`,`QPQRndYear`,`QPQRndMonth`,`QPLine`,`QPPart`,`QPDep`,`QPBand`,`QPSubBand`,`QPPos`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8

CREATE TABLE `ms_per` (
  `PerCompany` varchar(3) NOT NULL COMMENT 'รหัสบริษัท/สาขา',
  `PerCode` varchar(8) NOT NULL COMMENT 'รหัสพนักงาน',
  `PerIdenNo` varchar(13) DEFAULT NULL COMMENT 'เลขประจำตัว',
  `PerTitle` varchar(3) DEFAULT NULL COMMENT 'รหัสคำนำหน้า',
  `PerFName` varchar(30) DEFAULT NULL COMMENT 'ชื่อ',
  `PerLName` varchar(30) DEFAULT NULL COMMENT 'นามสกุล',
  `PerEName` varchar(64) DEFAULT NULL COMMENT 'ชื่อภาษาอังกฤษ',
  `PerCGroup` char(6) DEFAULT NULL COMMENT 'รหัสหน่วยงาน',
  `PerWorkLine` char(2) DEFAULT NULL COMMENT 'รหัสสายงาน',
  `PerPart` varchar(4) DEFAULT NULL COMMENT 'ฝ่าย',
  `PerDep` varchar(4) DEFAULT NULL COMMENT 'รหัสแผนก',
  `PerPos` varchar(4) DEFAULT NULL COMMENT 'รหัสตำแหน่ง',
  `PerGroup` varchar(1) DEFAULT NULL COMMENT 'รหัสกลุ่มพนักงาน',
  `PerEmploying` varchar(1) DEFAULT NULL COMMENT 'Y=อยู่ในบริษัท',
  `PerTravel` char(1) DEFAULT NULL COMMENT 'รหัสการเดินทาง',
  `PerTravCode` char(2) DEFAULT NULL COMMENT 'รหัสสายรถ',
  `PerRoomNo` varchar(3) DEFAULT NULL COMMENT 'เลขห้องพัก',
  `PerBand` char(2) DEFAULT '0' COMMENT 'รหัส Band พนักงาน',
  `PerSubBand` char(3) DEFAULT '0' COMMENT 'รหัส Band พนักงาน',
  `PerType` char(3) DEFAULT NULL COMMENT 'ประเภทพนักงาน',
  `PerShift` char(1) DEFAULT NULL COMMENT 'ช่วงเวลาทำงาน (กะ)',
  `PerShiftTime` char(3) DEFAULT '' COMMENT 'รหัส กะ(เวลา) เข้างาน (tb_workshift)',
  `PerDuty` varchar(4) DEFAULT NULL COMMENT 'รหัสหน้าที่',
  `PerDutyDesc` varchar(25) DEFAULT NULL,
  `PerD_M` char(1) DEFAULT 'D' COMMENT 'D:รายวัน  M:รายเดือน',
  `PerQPay` int(1) DEFAULT '2' COMMENT 'จำนวนงวดจ่าย (ต่อเดือน)',
  `PerTPay` char(1) DEFAULT '1' COMMENT 'จ่ายโดย  1:เข้าบัญชี    2:เงินสด',
  `PerQHrPerDay` float(2,0) DEFAULT '8' COMMENT 'จำนวนชั่วโมงทำงาน/วัน (นำมาคำนวณค่าแรง/ช.ม.)',
  `PerQDPerWeek` float(1,0) DEFAULT '6' COMMENT 'จำนวนวันทำงาน/สัปดาห์',
  `PerSoFlag` char(1) DEFAULT 'Y' COMMENT 'Y=เคยยื่นแบบ สปส',
  `PerSoStatus` char(1) DEFAULT 'Y' COMMENT 'Y:เข้าระบบประกันสังคม',
  `PerForeign` char(1) DEFAULT NULL COMMENT 'Y:ต่างด้าว',
  `BeginDate` varchar(6) DEFAULT NULL,
  `PerBeginDate` date DEFAULT NULL COMMENT 'วันเริ่มงาน',
  `BeginFull` varchar(8) DEFAULT NULL,
  `FillDate` varchar(6) DEFAULT NULL,
  `PerFillDate` date DEFAULT NULL COMMENT 'วันบรรจุงาน',
  `EndDate` varchar(6) DEFAULT NULL,
  `PerEndDate` date DEFAULT NULL COMMENT 'วันพ้นสภาพพนักงาน',
  `PerEndCode` char(2) DEFAULT '-' COMMENT 'รหัสการออก/พ้นสภาพพนักงาน',
  `PerEndLevel` char(2) DEFAULT '' COMMENT 'ระดับการพ้นสภาพ',
  `PerQuota` char(1) DEFAULT NULL COMMENT 'โควต้า(อัตรากำลังคน) 1:ชาย 2:หญิง  3:ชายหรือหญิง',
  `PerQuotaNo` varchar(10) DEFAULT NULL COMMENT 'เลขที่ใบขออัตรากำลัง',
  `PerPictFile` varchar(50) DEFAULT NULL COMMENT 'ชื่อไฟล์รูปภาพ',
  `PerLastComp` varchar(35) DEFAULT NULL COMMENT 'ที่ทำงานก่อนหน้านี้',
  `PerSkill` char(1) DEFAULT NULL COMMENT 'รหัสฝีมือ',
  `PerCutShirt` char(1) DEFAULT NULL COMMENT 'มีประกันเสื้อ',
  `PerISO` char(1) DEFAULT 'Y' COMMENT 'Y=เข้าระบบ ISO   N=ไม่เข้า',
  `PerISOType` char(1) DEFAULT '0' COMMENT 'ประเภท ISO',
  `PerTransfer` char(1) DEFAULT '-' COMMENT '9=โอนแล้ว',
  `sPerIdenEDate` varchar(8) DEFAULT NULL,
  `PerIdenEDate` date DEFAULT NULL COMMENT 'วันบัตรหมดอายุ',
  `per_user` varchar(10) DEFAULT NULL,
  `per_comid` varchar(15) DEFAULT NULL,
  `per_update` datetime DEFAULT NULL,
  PRIMARY KEY (`PerCompany`,`PerCode`),
  KEY `PerIdenNo` (`PerIdenNo`) USING BTREE,
  KEY `PerCode` (`PerCode`),
  KEY `PerEmploying` (`PerEmploying`),
  KEY `PerCGroup` (`PerCGroup`),
  KEY `SrchQuota` (`PerWorkLine`,`PerPart`,`PerDep`,`PerPos`,`PerBand`,`PerSubBand`) USING BTREE
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='แฟ้มข้อมูลพนักงาน'

CREATE TABLE `tb_subbandpos` (
  `BPCompany` varchar(3) NOT NULL COMMENT 'รหัสบริษัท',
  `BPBand` varchar(2) NOT NULL COMMENT 'รหัสแบนด์',
  `BPSubBand` varchar(3) NOT NULL COMMENT 'รหัสซับแบนด์',
  `BPPosCode` varchar(4) NOT NULL COMMENT 'รหัสตำแหน่ง',
  PRIMARY KEY (`BPCompany`,`BPBand`,`BPSubBand`,`BPPosCode`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8

Looks like you need to add c1, c2 and c3 to the `group by` clause — Igoranze, Jun 27 '16 at 09:52
You actually don't have these columns (`c1,c2,c3`), rather you created alias of this name in inner query, that's why is not recognizing. — Drone, Jun 27 '16 at 09:53
@1000111 It doesn't work. Unknown column 'mp1.c1' in 'field list' — Red Romanov, Jun 27 '16 at 10:12
It should work. Probably you missed some where it's required to use. — 1000111, Jun 27 '16 at 10:14
@1000111 I would undelete that answer of yours. We can probably get that to work. If the OP shows the schema with a `show create table xyz` for each table. — Drew, Jun 27 '16 at 16:29
I may have posted some meaningless comment to you before which I deleted. Sorry about that. But I put the answer together separately and it matched yours. There was another user in here with a deleted answer with a name like yours. A user `10086` — Drew, Jun 27 '16 at 16:38
thx. It choked first on no table def for `tb_subbandpos` . No need to alert me, but if you could add it, I could continue at least past that. — Drew, Jun 28 '16 at 03:09

score 1 · Accepted Answer · edited May 23 '17 at 12:32

Try the following (note, I am still editing it to get the database name back in, I think 2 spots in front of table names).

But cleansing the column names through involving a derived table has done the trick. Derived tables have the wonderful side-effect of folding up values into the query that wraps it. In this case, it removes the group by forbidden-ness from on INSERT ON DUPLICATE UPDATE.

INSERT INTO tb_quotapos(QPCompany,QPQRndYear,QPQRndMonth,QPLine,QPPart,QPDep,QPBand,QPSubBand,QPPos,QPExist1,QPExist2,QPExist3)
select col1,col2,col3,col4,col5,col6,col7,col8,col9,col10,col11,col12
from
(   SELECT "G02" as col1,2016 as col2,6 as col3,
    mp.PerWorkLine as col4,mp.PerPart as col5,mp.PerDep as col6,
    PerBand as col7,PerSubBand as col8,mp.PerPos as col9,
    c1 as col10,c2 as col11,c3 as col12 
    FROM ms_per mp
    LEFT JOIN tb_subbandpos ON BPBand=mp.PerSubBand
    LEFT JOIN (SELECT PerWorkLine,PerPart,PerDep,PerPos,count(*) c1 FROM ms_per WHERE PerQuota=1 AND PerEmploying="Y" AND PerISO="Y"  AND PerCGroup = "01"  GROUP BY PerWorkLine,PerPart,PerDep,PerPos) mp1 ON mp.PerWorkLine=mp1.PerWorkLine AND mp.PerPart=mp1.PerPart AND mp.perdep=mp1.perdep AND mp.perpos=mp1.perpos
    LEFT JOIN (SELECT PerWorkLine,PerPart,PerDep,PerPos,count(*) c2 FROM ms_per WHERE PerQuota=2 AND PerEmploying="Y" AND PerISO="Y"  AND PerCGroup = "01"  GROUP BY PerWorkLine,PerPart,PerDep,PerPos) mp2 ON mp.PerWorkLine=mp2.PerWorkLine AND mp.PerPart=mp2.PerPart AND mp.perdep=mp2.perdep AND mp.perpos=mp2.perpos
    LEFT JOIN (SELECT PerWorkLine,PerPart,PerDep,PerPos,count(*) c3 FROM ms_per WHERE PerQuota=3 AND PerEmploying="Y" AND PerISO="Y"  AND PerCGroup = "01"  GROUP BY PerWorkLine,PerPart,PerDep,PerPos) mp3 ON mp.PerWorkLine=mp3.PerWorkLine AND mp.PerPart=mp3.PerPart AND mp.perdep=mp3.perdep AND mp.perpos=mp3.perpos
    WHERE mp.PerEmploying="Y"
    AND (c1>0 OR c2>0 OR c3>0)
    GROUP BY mp.PerWorkLine,mp.PerPart,mp.PerDep,PerBand,PerSubBand,mp.PerPos
) xDerived
ON DUPLICATE KEY UPDATE QPExist1=col10, QPExist2=col11, QPExist3=col12;

Version 2 (with the 2 places that seem to need payroll_ho.):

INSERT INTO payroll_ho.tb_quotapos(QPCompany,QPQRndYear,QPQRndMonth,QPLine,QPPart,QPDep,QPBand,QPSubBand,QPPos,QPExist1,QPExist2,QPExist3)
select col1,col2,col3,col4,col5,col6,col7,col8,col9,col10,col11,col12
from
(   SELECT "G02" as col1,2016 as col2,6 as col3,
    mp.PerWorkLine as col4,mp.PerPart as col5,mp.PerDep as col6,
    PerBand as col7,PerSubBand as col8,mp.PerPos as col9,
    c1 as col10,c2 as col11,c3 as col12 
    FROM ms_per mp
    LEFT JOIN payroll_ho.tb_subbandpos ON BPBand=mp.PerSubBand
    LEFT JOIN (SELECT PerWorkLine,PerPart,PerDep,PerPos,count(*) c1 FROM ms_per WHERE PerQuota=1 AND PerEmploying="Y" AND PerISO="Y"  AND PerCGroup = "01"  GROUP BY PerWorkLine,PerPart,PerDep,PerPos) mp1 ON mp.PerWorkLine=mp1.PerWorkLine AND mp.PerPart=mp1.PerPart AND mp.perdep=mp1.perdep AND mp.perpos=mp1.perpos
    LEFT JOIN (SELECT PerWorkLine,PerPart,PerDep,PerPos,count(*) c2 FROM ms_per WHERE PerQuota=2 AND PerEmploying="Y" AND PerISO="Y"  AND PerCGroup = "01"  GROUP BY PerWorkLine,PerPart,PerDep,PerPos) mp2 ON mp.PerWorkLine=mp2.PerWorkLine AND mp.PerPart=mp2.PerPart AND mp.perdep=mp2.perdep AND mp.perpos=mp2.perpos
    LEFT JOIN (SELECT PerWorkLine,PerPart,PerDep,PerPos,count(*) c3 FROM ms_per WHERE PerQuota=3 AND PerEmploying="Y" AND PerISO="Y"  AND PerCGroup = "01"  GROUP BY PerWorkLine,PerPart,PerDep,PerPos) mp3 ON mp.PerWorkLine=mp3.PerWorkLine AND mp.PerPart=mp3.PerPart AND mp.perdep=mp3.perdep AND mp.perpos=mp3.perpos
    WHERE mp.PerEmploying="Y"
    AND (c1>0 OR c2>0 OR c3>0)
    GROUP BY mp.PerWorkLine,mp.PerPart,mp.PerDep,PerBand,PerSubBand,mp.PerPos
) xDerived
ON DUPLICATE KEY UPDATE QPExist1=col10, QPExist2=col11, QPExist3=col12;

The derived table was necessary due to the Group By. From the Manual Page INSERT ... SELECT Syntax:

In the values part of ON DUPLICATE KEY UPDATE, you can refer to columns in other tables, as long as you do not use GROUP BY in the SELECT part. One side effect is that you must qualify nonunique column names in the values part.

Credit to Justin in his answer here. I did however give an updated link to the manual page above.

Yes this is the answer I wanted! Thank you very much. If you got time maybe you can help with another question here. Your help will be greatly appreciated. http://stackoverflow.com/questions/38047673/how-to-make-multiple-left-joins-with-or-fully-use-a-composite-index — Red Romanov, Jun 28 '16 at 03:51
Will do after a little food. Glad it worked. I was planning a wrapper of a derived but frankly didn't think it was necessary until I read Justin's comment after a google. — Drew, Jun 28 '16 at 03:52
Ah, that question. I was thinking about getting back to that one. Saw it earlier. — Drew, Jun 28 '16 at 04:07

What is wrong with this INSERT INTO SELECT statement?

1 Answers1

Linked