Problem with Update query

**Miguel61** · Nov 25, 2011 02:06

Hi there.

I need your appreciated help.

I have this records in the mysql table `listfiles_2011_11_25`:

Code:

CODE_A	myDate	max_STATUS	max_date	INC_H2	INC_CH4	INC_C2H6	INC_C2H4	INC_C2H2	INC_CO	INC_CO2
5550	2005-10-27	ALERT	2009-07-28	-0.022	0.067	0.044	0.089	0.022	0.422	11.267
5550	2003-10-07	OK	2005-10-27	-3.500	-5.917	-4.500	-4.625	0.125	-5.542	-60.250

This is the output with this query:

Code:

SELECT  
  t.CODE_A 
, t.myDate

, m.max_STATUS 
, m.max_date 

, m.max_H2_PPM , t.H2_PPM 
, FORMAT((max_H2_PPM-t.H2_PPM)/PERIOD_DIFF(EXTRACT(YEAR_MONTH FROM m.max_date) 
, EXTRACT(YEAR_MONTH FROM t.myDate)),3) inc_H2 

, m.max_CH4_PPM , t.CH4_PPM 
, FORMAT((max_CH4_PPM-t.CH4_PPM)/PERIOD_DIFF(EXTRACT(YEAR_MONTH FROM m.max_date) 
, EXTRACT(YEAR_MONTH FROM t.myDate)),3) inc_CH4 

, m.max_C2H6_PPM , t.C2H6_PPM 
, FORMAT((max_C2H6_PPM-t.C2H6_PPM)/PERIOD_DIFF(EXTRACT(YEAR_MONTH FROM m.max_date) 
, EXTRACT(YEAR_MONTH FROM t.myDate)),3) inc_C2H6 

, m.max_C2H4_PPM , t.C2H4_PPM 
, FORMAT((max_C2H4_PPM-t.C2H4_PPM)/PERIOD_DIFF(EXTRACT(YEAR_MONTH FROM m.max_date) 
, EXTRACT(YEAR_MONTH FROM t.myDate)),3) inc_C2H4 

, m.max_C2H2_PPM , t.C2H2_PPM 
, FORMAT((max_C2H2_PPM-t.C2H2_PPM)/PERIOD_DIFF(EXTRACT(YEAR_MONTH FROM m.max_date) 
, EXTRACT(YEAR_MONTH FROM t.myDate)),3) inc_C2H2 

, m.max_CO_PPM , t.CO_PPM , FORMAT((max_CO_PPM-t.CO_PPM)/PERIOD_DIFF(EXTRACT(YEAR_MONTH FROM m.max_date) 
, EXTRACT(YEAR_MONTH FROM t.myDate)),3) inc_CO 

, m.max_CO2_PPM , t.CO2_PPM , FORMAT((max_CO2_PPM-t.CO2_PPM)/PERIOD_DIFF(EXTRACT(YEAR_MONTH FROM m.max_date) 
, EXTRACT(YEAR_MONTH FROM t.myDate)),3) inc_CO2 

FROM 
( SELECT 
  CODE_A 
, MAX(myDate) max_date
, MAX(STATUS) max_STATUS 
, MAX(H2_PPM) max_H2_PPM 
, MAX(CH4_PPM) max_CH4_PPM 
, MAX(C2H6_PPM) max_C2H6_PPM 
, MAX(C2H4_PPM) max_C2H4_PPM 
, MAX(C2H2_PPM) max_C2H2_PPM 
, MAX(CO_PPM) max_CO_PPM 
, MAX(CO2_PPM) max_CO2_PPM 
FROM 
listfiles_2011_11_25 
GROUP BY CODE_A, myDate) m 
INNER JOIN listfiles_2011_11_25 t ON m.CODE_A = t.CODE_A 
AND (m.max_date > t.myDate)
AND t.CODE_A = '5550' 
GROUP BY t.CODE_A, myDate
ORDER BY t.ID DESC;

I have to compare values `inc_H2, inc_CH4, inc_C2H6, inc_C2H4, inc_C2H2, inc_CO, inc_CO2` with this values:

Code:

inc_H2	inc_CH4	inc_C2H6	inc_C2H4	inc_C2H2	inc_CO	inc_CO2
11	10	8		13		4		90	850

If only one value is out of range I need update the `field' STATUS of the mysql table `listfiles_2011_11_25` with `ALERT` else it's `OK`.

I try this query, but even nothing value is out of range the `field' STATUS not change from `ALERT` to `ok` in the first record.

What wrong?
Can you help me?

Code:

UPDATE listfiles_2011_11_25 t
INNER JOIN

( SELECT 
              CODE_A
            , MAX(STATUS) max_STATUS
            , MAX(myDate) max_date
            , MAX(H2_PPM) max_H2_PPM
            , MAX(CH4_PPM) max_CH4_PPM
            , MAX(C2H6_PPM) max_C2H6_PPM
            , MAX(C2H4_PPM) max_C2H4_PPM
            , MAX(C2H2_PPM) max_C2H2_PPM
            , MAX(CO_PPM) max_CO_PPM
            , MAX(CO2_PPM) max_CO2_PPM

FROM listfiles_2011_11_25
GROUP BY CODE_A, myDate) m

ON   m.CODE_A = t.CODE_A 
AND (m.max_date > t.myDate)
AND (t.CODE_A <>'' AND t.CODE_A <> '0')

SET
     , t.STATUS =
       IF(
       FORMAT((max_H2_PPM-t.H2_PPM)/PERIOD_DIFF(EXTRACT(YEAR_MONTH FROM m.max_date)
     , EXTRACT(YEAR_MONTH FROM t.myDate)),3) > 11 OR 
       
       FORMAT((max_CH4_PPM-t.CH4_PPM)/PERIOD_DIFF(EXTRACT(YEAR_MONTH FROM m.max_date)
     , EXTRACT(YEAR_MONTH FROM t.myDate)),3) > 10 OR 
       
       FORMAT((max_C2H6_PPM-t.C2H6_PPM)/PERIOD_DIFF(EXTRACT(YEAR_MONTH FROM m.max_date)
     , EXTRACT(YEAR_MONTH FROM t.myDate)),3) > 8 OR 

       FORMAT((max_C2H4_PPM-t.C2H4_PPM)/PERIOD_DIFF(EXTRACT(YEAR_MONTH FROM m.max_date)
     , EXTRACT(YEAR_MONTH FROM t.myDate)),3) > 13 OR 

       FORMAT((max_C2H2_PPM-t.C2H2_PPM)/PERIOD_DIFF(EXTRACT(YEAR_MONTH FROM m.max_date)
     , EXTRACT(YEAR_MONTH FROM t.myDate)),3) > 4 OR 

       FORMAT((max_CO_PPM-t.CO_PPM)/PERIOD_DIFF(EXTRACT(YEAR_MONTH FROM m.max_date)
     , EXTRACT(YEAR_MONTH FROM t.myDate)),3) > 90 OR 

       FORMAT((max_CO2_PPM-t.CO2_PPM)/PERIOD_DIFF(EXTRACT(YEAR_MONTH FROM m.max_date)
     , EXTRACT(YEAR_MONTH FROM t.myDate)),3) > 850
     , 'ALERT', 'ok')

WHERE t.CODE_A like '%5550%';

**r937** · Nov 25, 2011 06:52

if your query runs but doesn't update anything, this means that the data values you are checking are all okay

either that, or your checking calculations are incorrect

why are you using FORMAT with decimal places, when your comparisons are to whole numbers?

e.g. FORMAT((max_H2_PPM - t.H2_PPM) / PERIOD_DIFF(...),3) > 11

**r937** · Nov 25, 2011 06:58

by the way, if you specify this in your WHERE clause --

... WHERE t.CODE_A like '%5550%';

then you don't need this in your join --

... AND (t.CODE_A <>'' AND t.CODE_A <> '0')

**Miguel61** · Nov 25, 2011 09:04

Hi Sir: I record your suggestions and try this query:

PHP Code:


UPDATE listfiles_2011_11_25 t

INNER JOIN



( SELECT 

              CODE_A

            , MAX(STATUS) max_STATUS

            , MAX(myDate) max_date

            , MAX(H2_PPM) max_H2_PPM

            , MAX(CH4_PPM) max_CH4_PPM

            , MAX(C2H6_PPM) max_C2H6_PPM

            , MAX(C2H4_PPM) max_C2H4_PPM

            , MAX(C2H2_PPM) max_C2H2_PPM

            , MAX(CO_PPM) max_CO_PPM

            , MAX(CO2_PPM) max_CO2_PPM



FROM listfiles_2011_11_25

GROUP BY CODE_A, myDate) m



ON   m.CODE_A = t.CODE_A 

AND (m.max_date > t.myDate)



SET

     , t.STATUS =

       IF(

       (max_H2_PPM-t.H2_PPM)/PERIOD_DIFF(EXTRACT(YEAR_MONTH FROM m.max_date)

     , EXTRACT(YEAR_MONTH FROM t.myDate)) > 11 OR 

       

       (max_CH4_PPM-t.CH4_PPM)/PERIOD_DIFF(EXTRACT(YEAR_MONTH FROM m.max_date)

     , EXTRACT(YEAR_MONTH FROM t.myDate)) > 10 OR 

       

       (max_C2H6_PPM-t.C2H6_PPM)/PERIOD_DIFF(EXTRACT(YEAR_MONTH FROM m.max_date)

     , EXTRACT(YEAR_MONTH FROM t.myDate)) > 8 OR 



       (max_C2H4_PPM-t.C2H4_PPM)/PERIOD_DIFF(EXTRACT(YEAR_MONTH FROM m.max_date)

     , EXTRACT(YEAR_MONTH FROM t.myDate)) > 13 OR 



       (max_C2H2_PPM-t.C2H2_PPM)/PERIOD_DIFF(EXTRACT(YEAR_MONTH FROM m.max_date)

     , EXTRACT(YEAR_MONTH FROM t.myDate)) > 4 OR 



       (max_CO_PPM-t.CO_PPM)/PERIOD_DIFF(EXTRACT(YEAR_MONTH FROM m.max_date)

     , EXTRACT(YEAR_MONTH FROM t.myDate)) > 90 OR 



       (max_CO2_PPM-t.CO2_PPM)/PERIOD_DIFF(EXTRACT(YEAR_MONTH FROM m.max_date)

     , EXTRACT(YEAR_MONTH FROM t.myDate)) > 850

     , 'ALERT', 'ok')



WHERE t.CODE_A like '%5550%';

Output:
Affected rows: 0
Time: 1.781ms

But if I have this row:

Code:

CODE_A	myDate	max_STATUS	max_date	INC_H2	INC_CH4	INC_C2H6	INC_C2H4	INC_C2H2	INC_CO	INC_CO2
5550	2005-10-27	ALERT	2009-07-28	-0.022	0.067	0.044	0.089	0.022	0.422	11.267

And all values `inc...` are in this range:

Code:

inc_H2	inc_CH4	inc_C2H6	inc_C2H4	inc_C2H2	inc_CO	inc_CO2
11	10	8		13		4		90	850

Why the `STATUS` not change in this row from ALERT to OK ?

I need to compare rows in table `listfiles_2011_11_25` for CODE_A.

**r937** · Nov 25, 2011 09:10

i have no idea, because i don't know your data

by the way, why do you have GROUP BY BY CODE_A, myDate in the "m" subquery?

you do realize that MAX(mydate) can have only one value for each mydate, right?

**Miguel61** · Nov 25, 2011 09:51

It's true..., but I don't have change in the output of the query update:

PHP Code:


UPDATE listfiles_2011_11_25 t 
INNER JOIN 

( SELECT  
              CODE_A 
            , MAX(STATUS) max_STATUS 
            , MAX(myDate) max_date 
            , MAX(H2_PPM) max_H2_PPM 
            , MAX(CH4_PPM) max_CH4_PPM 
            , MAX(C2H6_PPM) max_C2H6_PPM 
            , MAX(C2H4_PPM) max_C2H4_PPM 
            , MAX(C2H2_PPM) max_C2H2_PPM 
            , MAX(CO_PPM) max_CO_PPM 
            , MAX(CO2_PPM) max_CO2_PPM 

FROM listfiles_2011_11_25 
GROUP BY CODE_A) m 

ON   m.CODE_A = t.CODE_A  
AND (m.max_date > t.myDate) 

SET 
     , t.STATUS = 
       IF( 
       (max_H2_PPM-t.H2_PPM)/PERIOD_DIFF(EXTRACT(YEAR_MONTH FROM m.max_date) 
     , EXTRACT(YEAR_MONTH FROM t.myDate)) > 11 OR  
        
       (max_CH4_PPM-t.CH4_PPM)/PERIOD_DIFF(EXTRACT(YEAR_MONTH FROM m.max_date) 
     , EXTRACT(YEAR_MONTH FROM t.myDate)) > 10 OR  
        
       (max_C2H6_PPM-t.C2H6_PPM)/PERIOD_DIFF(EXTRACT(YEAR_MONTH FROM m.max_date) 
     , EXTRACT(YEAR_MONTH FROM t.myDate)) > 8 OR  

       (max_C2H4_PPM-t.C2H4_PPM)/PERIOD_DIFF(EXTRACT(YEAR_MONTH FROM m.max_date) 
     , EXTRACT(YEAR_MONTH FROM t.myDate)) > 13 OR  

       (max_C2H2_PPM-t.C2H2_PPM)/PERIOD_DIFF(EXTRACT(YEAR_MONTH FROM m.max_date) 
     , EXTRACT(YEAR_MONTH FROM t.myDate)) > 4 OR  

       (max_CO_PPM-t.CO_PPM)/PERIOD_DIFF(EXTRACT(YEAR_MONTH FROM m.max_date) 
     , EXTRACT(YEAR_MONTH FROM t.myDate)) > 90 OR  

       (max_CO2_PPM-t.CO2_PPM)/PERIOD_DIFF(EXTRACT(YEAR_MONTH FROM m.max_date) 
     , EXTRACT(YEAR_MONTH FROM t.myDate)) > 850 
     , 'ALERT', 'ok') 

WHERE t.CODE_A like '%5550%';

Output:
Affected rows: 0
Time: 1.781ms

This is my table mysql:

Code:

/*
Navicat MySQL Data Transfer

Source Server         : _myServer
Source Server Version : 50045
Source Host           : localhost:3306
Source Database       : _mydb

Target Server Type    : MYSQL
Target Server Version : 50045
File Encoding         : 65001

Date: 2011-11-25 16:48:03
*/

SET FOREIGN_KEY_CHECKS=0;
-- ----------------------------
-- Table structure for `listfiles_2011_11_25_copy`
-- ----------------------------
DROP TABLE IF EXISTS `listfiles_2011_11_25_copy`;
CREATE TABLE `listfiles_2011_11_25_copy` (
  `ID` int(10) NOT NULL auto_increment,
  `CODE_A` varchar(255) default NULL,
  `myDate` date default NULL,
  `CO_PPM` decimal(10,2) default NULL,
  `CO2_PPM` decimal(10,2) default NULL,
  `H2_PPM` decimal(10,2) default NULL,
  `CH4_PPM` decimal(10,2) default NULL,
  `C2H6_PPM` decimal(10,2) default NULL,
  `C2H4_PPM` decimal(10,2) default NULL,
  `C2H2_PPM` decimal(10,2) default NULL,
  `STATUS` varchar(10) default NULL,
  PRIMARY KEY  (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=6012 DEFAULT CHARSET=latin1;

-- ----------------------------
-- Records of listfiles_2011_11_25_copy
-- ----------------------------
INSERT INTO listfiles_2011_11_25_copy VALUES ('2309', '5550', '2003-10-07', '223.00', '2256.00', '97.00', '144.00', '114.00', '114.00', '0.00', 'ok');
INSERT INTO listfiles_2011_11_25_copy VALUES ('3408', '5550', '2005-10-27', '90.00', '810.00', '13.00', '2.00', '6.00', '3.00', '3.00', 'ok');
INSERT INTO listfiles_2011_11_25_copy VALUES ('6011', '5550', '2009-07-28', '109.00', '1317.00', '12.00', '5.00', '8.00', '7.00', '4.00', 'alert');

**Miguel61** · Nov 25, 2011 11:29

Error copy/paste in the query update of the last post; this is correct.
Sorry Sir.

PHP Code:


UPDATE listfiles_2011_11_25 t 
INNER JOIN 

( SELECT  
              CODE_A 
            , MAX(STATUS) max_STATUS 
            , MAX(myDate) max_date 
            , MAX(H2_PPM) max_H2_PPM 
            , MAX(CH4_PPM) max_CH4_PPM 
            , MAX(C2H6_PPM) max_C2H6_PPM 
            , MAX(C2H4_PPM) max_C2H4_PPM 
            , MAX(C2H2_PPM) max_C2H2_PPM 
            , MAX(CO_PPM) max_CO_PPM 
            , MAX(CO2_PPM) max_CO2_PPM 

FROM listfiles_2011_11_25 
GROUP BY CODE_A) m 

ON   m.CODE_A = t.CODE_A  
AND (m.max_date > t.myDate) 

SET 
       t.STATUS = 
       IF( 
       (max_H2_PPM-t.H2_PPM)/PERIOD_DIFF(EXTRACT(YEAR_MONTH FROM m.max_date) 
     , EXTRACT(YEAR_MONTH FROM t.myDate)) > 11 OR  
        
       (max_CH4_PPM-t.CH4_PPM)/PERIOD_DIFF(EXTRACT(YEAR_MONTH FROM m.max_date) 
     , EXTRACT(YEAR_MONTH FROM t.myDate)) > 10 OR  
        
       (max_C2H6_PPM-t.C2H6_PPM)/PERIOD_DIFF(EXTRACT(YEAR_MONTH FROM m.max_date) 
     , EXTRACT(YEAR_MONTH FROM t.myDate)) > 8 OR  

       (max_C2H4_PPM-t.C2H4_PPM)/PERIOD_DIFF(EXTRACT(YEAR_MONTH FROM m.max_date) 
     , EXTRACT(YEAR_MONTH FROM t.myDate)) > 13 OR  

       (max_C2H2_PPM-t.C2H2_PPM)/PERIOD_DIFF(EXTRACT(YEAR_MONTH FROM m.max_date) 
     , EXTRACT(YEAR_MONTH FROM t.myDate)) > 4 OR  

       (max_CO_PPM-t.CO_PPM)/PERIOD_DIFF(EXTRACT(YEAR_MONTH FROM m.max_date) 
     , EXTRACT(YEAR_MONTH FROM t.myDate)) > 90 OR  

       (max_CO2_PPM-t.CO2_PPM)/PERIOD_DIFF(EXTRACT(YEAR_MONTH FROM m.max_date) 
     , EXTRACT(YEAR_MONTH FROM t.myDate)) > 850 
     , 'ALERT', 'ok') 

WHERE t.CODE_A like '%5550%';

**r937** · Nov 25, 2011 11:55

okay, i looked at your test data

Code:

SELECT t.CODE_A
     , t.id
     , t.status
     , PERIOD_DIFF(EXTRACT(YEAR_MONTH FROM m.max_date),
                   EXTRACT(YEAR_MONTH FROM t.myDate))   AS period_diff
     , (max_H2_PPM-t.H2_PPM) /
         PERIOD_DIFF(EXTRACT(YEAR_MONTH FROM m.max_date),
                     EXTRACT(YEAR_MONTH FROM t.myDate))   AS diff_11        
     , (max_CH4_PPM-t.CH4_PPM) /
         PERIOD_DIFF(EXTRACT(YEAR_MONTH FROM m.max_date),
                     EXTRACT(YEAR_MONTH FROM t.myDate))   AS diff_10        
     , (max_C2H6_PPM-t.C2H6_PPM) /
         PERIOD_DIFF(EXTRACT(YEAR_MONTH FROM m.max_date),
                     EXTRACT(YEAR_MONTH FROM t.myDate))   AS diff_8
     , (max_C2H4_PPM-t.C2H4_PPM) /
         PERIOD_DIFF(EXTRACT(YEAR_MONTH FROM m.max_date),
                     EXTRACT(YEAR_MONTH FROM t.myDate))   AS diff_13
     , (max_C2H2_PPM-t.C2H2_PPM) /
         PERIOD_DIFF(EXTRACT(YEAR_MONTH FROM m.max_date),
                     EXTRACT(YEAR_MONTH FROM t.myDate))   AS diff_4
     , (max_CO_PPM-t.CO_PPM) /
         PERIOD_DIFF(EXTRACT(YEAR_MONTH FROM m.max_date),
                     EXTRACT(YEAR_MONTH FROM t.myDate))   AS diff_90
     , (max_CO2_PPM-t.CO2_PPM) /
         PERIOD_DIFF(EXTRACT(YEAR_MONTH FROM m.max_date),
                     EXTRACT(YEAR_MONTH FROM t.myDate))   AS diff_850 
  FROM ( SELECT CODE_A 
              , MAX(STATUS) max_STATUS 
              , MAX(myDate) max_date 
              , MAX(H2_PPM) max_H2_PPM 
              , MAX(CH4_PPM) max_CH4_PPM 
              , MAX(C2H6_PPM) max_C2H6_PPM 
              , MAX(C2H4_PPM) max_C2H4_PPM 
              , MAX(C2H2_PPM) max_C2H2_PPM 
              , MAX(CO_PPM) max_CO_PPM 
              , MAX(CO2_PPM) max_CO2_PPM 
           FROM listfiles_2011_11_25_copy 
         GROUP 
             BY CODE_A ) m 
INNER
  JOIN listfiles_2011_11_25_copy AS t
    ON m.CODE_A = t.CODE_A     
   AND m.max_date > t.myDate 


CODE_A   id   status   period_diff   diff_11    diff_10    diff_8     diff_13    diff_4     diff_90   diff_850
5550   2309   ok           69       0.000000   0.000000   0.000000   0.000000   0.057971   0.000000   0.000000
5550   3408   ok           45       1.866667   3.155556   2.400000   2.466667   0.022222   2.955556   32.133333

if you're wondering where id 6011 went, you filtered it out using this --

Code:

   
   AND m.max_date > t.myDate

**Miguel61** · Nov 25, 2011 12:16

Thank Sir I understand your test data and now I filter with:

Code:

   AND m.max_date >= t.myDate

Remove this condition, the output is identic.

But in the new output the id 6011 is not examined... this is wrong... perhaps my solution is wrong... you have some ideas for my problem? thanks for your help.

Code:

CODE_A	id	status	period_diff	diff_11		diff_10		diff_8		diff_13		diff_4		diff_90		diff_850
5550	2309	ok		69	0,000000	0,000000	0,000000	0,000000	0,057971	0,000000	0,000000
5550	3408	ok		45	1,866667	3,155556	2,400000	2,466667	0,022222	2,955556	32,133333
5550	6011	alert		0

**r937** · Nov 25, 2011 12:21

you say id 6011 is not "examined" but it is

the period difference, of course, is zero, and if you divide by zero, you get NULL, and navicat is probably just not showing you the nulls

also, stop and think for a moment about this --

Code:

AND m.max_date >= t.myDate

since the max_date is constructed by using MAX(mydate), you can reasonably assume that the max date will ~always~ be equal to or greater than any of the dates it was constructed from

so you can drop that line

**Miguel61** · Nov 25, 2011 12:41

If you do not consider MAX(mydate) how to search backward same code_a in the table ?
I am ready to follow your reasoning... thanks...

**r937** · Nov 25, 2011 12:46

i'm sorry, i have no idea why you need to "search backward"

you must remember that only you know your data well enough to design the application

**Miguel61** · Nov 25, 2011 13:11

I don't know if I was wrong to use MAX(mydate)... and if there are other solutions
My data I have sent...

**Miguel61** · Nov 26, 2011 06:12

Hi Sir Rudy:

I try this query update when changed the condition:

Code:

AND (m.max_date > t.myDate)

With:

Code:

AND (m.max_date >= t.myDate)

and the output is correct:

Code:

CODE_A	id	status
5550	2309	ok
5550	3408	ok
5550	6011	ok

Affected rows: 1
Time: 0.015ms

This is your suggestion?

Code:

UPDATE listfiles_2011_11_25 t 
INNER JOIN 

( SELECT  
              CODE_A 
            , MAX(STATUS) max_STATUS 
            , MAX(myDate) max_date 
            , MAX(H2_PPM) max_H2_PPM 
            , MAX(CH4_PPM) max_CH4_PPM 
            , MAX(C2H6_PPM) max_C2H6_PPM 
            , MAX(C2H4_PPM) max_C2H4_PPM 
            , MAX(C2H2_PPM) max_C2H2_PPM 
            , MAX(CO_PPM) max_CO_PPM 
            , MAX(CO2_PPM) max_CO2_PPM 

FROM listfiles_2011_11_25 
GROUP BY CODE_A) m 

ON   m.CODE_A = t.CODE_A  
AND (m.max_date >= t.myDate) 

SET 
       t.STATUS = 
       IF( 
       (max_H2_PPM-t.H2_PPM)/PERIOD_DIFF(EXTRACT(YEAR_MONTH FROM m.max_date) 
     , EXTRACT(YEAR_MONTH FROM t.myDate)) > 11 OR  
        
       (max_CH4_PPM-t.CH4_PPM)/PERIOD_DIFF(EXTRACT(YEAR_MONTH FROM m.max_date) 
     , EXTRACT(YEAR_MONTH FROM t.myDate)) > 10 OR  
        
       (max_C2H6_PPM-t.C2H6_PPM)/PERIOD_DIFF(EXTRACT(YEAR_MONTH FROM m.max_date) 
     , EXTRACT(YEAR_MONTH FROM t.myDate)) > 8 OR   

       (max_C2H4_PPM-t.C2H4_PPM)/PERIOD_DIFF(EXTRACT(YEAR_MONTH FROM m.max_date) 
     , EXTRACT(YEAR_MONTH FROM t.myDate)) > 13 OR  

       (max_C2H2_PPM-t.C2H2_PPM)/PERIOD_DIFF(EXTRACT(YEAR_MONTH FROM m.max_date) 
     , EXTRACT(YEAR_MONTH FROM t.myDate)) > 4 OR  

       (max_CO_PPM-t.CO_PPM)/PERIOD_DIFF(EXTRACT(YEAR_MONTH FROM m.max_date) 
     , EXTRACT(YEAR_MONTH FROM t.myDate)) > 90 OR  

       (max_CO2_PPM-t.CO2_PPM)/PERIOD_DIFF(EXTRACT(YEAR_MONTH FROM m.max_date) 
     , EXTRACT(YEAR_MONTH FROM t.myDate)) > 850

     , 'ALERT', 'ok') 

WHERE t.CODE_A like '%5550%';

**r937** · Nov 26, 2011 07:07

Originally Posted by Miguel61

This is your suggestion?

no, it isn't

but since it works, vwalah

**Miguel61** · Nov 26, 2011 14:23

Would be interesting to know your solution... do you not agree ? I can learn always from you many beautiful things...

**r937** · Nov 26, 2011 14:48

Originally Posted by Miguel61

Would be interesting to know your solution...

sorry, i don't have one

i'm still trying to understand the question

**Miguel61** · Nov 27, 2011 02:59

ok, do not understand ?

**r937** · Nov 27, 2011 05:19

Originally Posted by Miguel61

ok, do not understand ?

that's right

i do not understand why your tables are designed the way they are, and i do not know what your queries are trying to achieve

**Miguel61** · Nov 28, 2011 03:36

well I'll try an explanation... hope for success.

I'm trying to compare rows in table mysql for field `code_A`.

From this comparison I need calculate:

number of months between a first and the next record (`myDate`)
e.g. : record #3408 compared from record #2309: 23 month (A);
the difference with `CO_PPM` field
e.g. : record #3408 compared from record #2309 : -133 (B);

and finally this algorithm:

C_CO_PPM = (B)/(A)
e.g. : C_CO_PPM = -133/23 = -5,78

If the algorithm (C_CO_PPM) it's > of 90 I need update the field `Status` with `ALERT` else 'ok'.

In my example I need update row with 'OK' in the field `Status` becuse the value -5,78 it's < 90 value.

**r937** · Nov 28, 2011 07:04

sorry, i am still a long way from understanding, and to be honest, it's not going to help you very much

you realize those MAX values that you produce in the "m" subquery don't all come from the same row, right?

so if you're trying to compare, for example, "1.number of months between a first and the next record" you aren't getting consecutive rows at all

**Miguel61** · Nov 28, 2011 07:17

Ok I understand...

But perhaps explain step by step.

how to compare two records when they have the same code_A ?

**r937** · Nov 28, 2011 07:20

Originally Posted by Miguel61

how to compare two records when they have the same code_A ?

how do you identify those two rows?

sure, you have a primary key on the table, but it's only an auto_increment, and it's useless for this task

so how would you suggest identifying consecutive rows? how are you going to find the id numbers of the rows you want to compare?

**Miguel61** · Nov 28, 2011 07:30

Good question Sir.

Each row is different and for each row in the table I have:

1) the code_A
2) myDate
3) CO_PPM
4) CO2_PPM
5) H2_PPM
6) CH4_PPM
7) C2H6_PPM
8) C2H4_PPM
9) C2H2_PPM
10) STATUS

The field `myDate` for each row with the same `code_a` is crucial for identifying consecutive rows.

I don't say that my solution is right or final.

**r937** · Nov 28, 2011 08:15

now that you have finally revealed that you're not interested in the collective MAXes of your data columns, but rather, want consecutive rows compared, i'm afraid i just don't have the time to walk you through a complete rebuilding of your query

if you have specific issues, like a syntax error or something, maybe i can take a look

otherwise, you're going to have to rebuild the query yourself

one other factor to consider is that comparing consecutive rows is something that is often accomplished far more efficiently in the application, i.e. you just return sorted rows to the application and the application compares one row to the next

User Tag List

Thread: Problem with Update query

Thread Tools

Display

Problem with Update query

Bookmarks

Bookmarks

Posting Permissions