0

i have a problem with follwing query

INSERT IGNORE INTO cartellino_anno (iddipendente, mese, ordinarie,effettive,assenze,straordinari,ritardo,anticipo,richiestaassenza,richiestastraordinario,richiestapresenza)
SELECT
            1,
            om.mese AS mese,
            SUM( om.ordinarie ) /3600 ordinarie,
            SUM( om.secondi ) / 3600 effettive,
            ABS(SUM( om.assenze ) )/3600 assenze,
            SUM( om.straordinari )/3600  straordinari,
            SUM( om.ritardo ) /3600  ritardo,
            SUM( om.anticipo ) /3600  anticipo,
            SUM( om.richiestaassenza ) richiestaassenza,
            SUM( om.richiestastraordinario ) richiestastraordinario,
            SUM( om.richiestapresenza )  richiestapresenza 
FROM (SELECT idDipendente, mese, idRichiesta,
            @secondi := (COALESCE ( TIMESTAMPDIFF( SECOND, entrata1, uscita1 ), 0 )+ COALESCE ( TIMESTAMPDIFF( SECOND, entrata2, uscita2 ), 0 ) + COALESCE ( TIMESTAMPDIFF( SECOND, entrata3, uscita3 ), 0 )) AS secondi,
            @ordinarie := TIME_TO_SEC( ordinarie ) ordinarie,
            @differenza := ( @secondi - @ordinarie ) differenza,
            IF ( @differenza < 0, @differenza, NULL ) assenze,
            IF ( @differenza > 0, @differenza, NULL ) straordinari,
            TIME_TO_SEC( ritardo1 ) + TIME_TO_SEC( ritardo2 ) + TIME_TO_SEC( ritardo3 ) AS ritardo,
            TIME_TO_SEC( anticipo1 ) + TIME_TO_SEC( anticipo2 ) + TIME_TO_SEC( anticipo3 ) AS anticipo,
            richiestaassenza, richiestastraordinario,richiestapresenza 
            FROM v_ore_mese) om
GROUP BY om.mese        
ON DUPLICATE KEY UPDATE ordinarie=ordinarie, effettive = effettive ;    

it work correctly in insert but not in update

i have tried many solution like

.. SUM( om.ordinarie ) /3600 sumordinarie, ..

and

 ordinarie=sumordinarie

or

 ordinarie=om.sumordinarie

but does not work.. anyone can help?

1054 - Unknown column 'om.sumordinarie' in 'field list'

update

following some indications i changed my query to

INSERT IGNORE INTO cartellino_anno (iddipendente, mese, ordinarie,effettive,assenze,straordinari,ritardo,anticipo,richiestaassenza,richiestastraordinario,richiestapresenza)
SELECT
            1,
            om.mese AS minmese,
            SUM( om.ordinarie ) /3600 sumordinarie,
            SUM( om.secondi ) / 3600 sumeffettive,
            ABS(SUM( om.assenze ) )/3600 assenze,
            SUM( om.straordinari )/3600  straordinari,
            SUM( om.ritardo ) /3600  ritardo,
            SUM( om.anticipo ) /3600  anticipo,
            SUM( om.richiestaassenza ) richiestaassenza,
            SUM( om.richiestastraordinario ) richiestastraordinario,
            SUM( om.richiestapresenza )  richiestapresenza 
FROM (SELECT idDipendente, mese, idRichiesta,
            @secondi := (COALESCE ( TIMESTAMPDIFF( SECOND, entrata1, uscita1 ), 0 )+ COALESCE ( TIMESTAMPDIFF( SECOND, entrata2, uscita2 ), 0 ) + COALESCE ( TIMESTAMPDIFF( SECOND, entrata3, uscita3 ), 0 )) AS secondi,
            @ordinarie := TIME_TO_SEC( ordinarie ) ordinarie,
            @differenza := ( @secondi - @ordinarie ) differenza,
            IF ( @differenza < 0, @differenza, NULL ) assenze,
            IF ( @differenza > 0, @differenza, NULL ) straordinari,
            TIME_TO_SEC( ritardo1 ) + TIME_TO_SEC( ritardo2 ) + TIME_TO_SEC( ritardo3 ) AS ritardo,
            TIME_TO_SEC( anticipo1 ) + TIME_TO_SEC( anticipo2 ) + TIME_TO_SEC( anticipo3 ) AS anticipo,
            richiestaassenza, richiestastraordinario,richiestapresenza 
            FROM v_ore_mese
            ORDER BY data ASC
            ) om
GROUP BY om.mese        
ON DUPLICATE KEY UPDATE ordinarie= om.sumordinarie, effettive = om.sumeffettive;

but still error 1054 - Unknown column 'om.sumordinarie' in 'field list' in update only, insert qoek correctly

here ddl of cartellino_anno

DROP TABLE IF EXISTS `cartellino_anno`;
CREATE TABLE `cartellino_anno`  (
  `idCartellino` int(11) NOT NULL AUTO_INCREMENT,
  `idDIpendente` int(11) NOT NULL,
  `mese` varchar(10) CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL,
  ....
  ....
  
  PRIMARY KEY (`idCartellino`) USING BTREE,
  UNIQUE INDEX `idx1`(`idDIpendente`) USING BTREE,
  UNIQUE INDEX `idx2`(`mese`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 99 CHARACTER SET = latin1 COLLATE = latin1_swedish_ci ROW_FORMAT = Dynamic;
  • Your subquery om is wrong. When you use UDV then you MUST specify ORDER BY clause by an expression which provides rows uniqueness - if not then rows processing order is unpredictable and the result become non-deterministic. – Akina Jul 30 '20 at 16:27
  • @Akina om i have tried to add a order by FROM v_ore_mese ORDER BY giorno ) om but i am still having Unknown column 'om.sumordinarie' in 'field list' – andymo Jul 30 '20 at 16:43
  • 1
    it work correctly in insert but not in update Please add full UPDATE (base and edited) query texts into the question after your editions description. Now it is unclear how it looks like, and what and where you have edited... And show cartellino_anno's DDL (may be shortened - primary and unique indices definitions is enough).. – Akina Jul 30 '20 at 17:31
  • Shouldn't the GROUP BY be in the subquery? – Rick James Jul 30 '20 at 20:02
  • Your query says on.sumordinarie, i.e. n instead of m – Lennart Jul 31 '20 at 8:52

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.