Problem with Update query

Miguel61 · November 25, 2011, 8:06am

Hi there.

I need your appreciated help.

I have this records in the mysql table listfiles_2011_11_25:


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:


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:


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_25withALERTelse it'sOK`.

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

What wrong?
Can you help me?


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 · November 25, 2011, 12:52pm

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 · November 25, 2011, 12:58pm

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 · November 25, 2011, 3:04pm

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

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_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:

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 · November 25, 2011, 3:10pm

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 · November 25, 2011, 3:51pm

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


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:


/*
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 · November 25, 2011, 5:29pm

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

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 · November 25, 2011, 5:55pm

okay, i looked at your test data

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 –

   
   AND m.max_date > t.myDate

Miguel61 · November 25, 2011, 6:16pm

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


   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_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 · November 25, 2011, 6:21pm

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 –

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 · November 25, 2011, 6:41pm

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 · November 25, 2011, 6:46pm

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 · November 25, 2011, 7:11pm

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

Miguel61 · November 26, 2011, 12:12pm

Hi Sir Rudy:

I try this query update when changed the condition:

AND (m.max_date > t.myDate)

With:

AND (m.max_date >= t.myDate)

and the output is correct:

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

Affected rows: 1
Time: 0.015ms

This is your suggestion?

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  
[COLOR="#FF0000"][B]AND (m.max_date >= t.myDate) [/B][/COLOR]

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 · November 26, 2011, 1:07pm

no, it isn’t

but since it works, vwalah

Miguel61 · November 26, 2011, 8:23pm

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

r937 · November 26, 2011, 8:48pm

sorry, i don’t have one

i’m still trying to understand the question

Miguel61 · November 27, 2011, 8:59am

ok, do not understand ?

r937 · November 27, 2011, 11:19am

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 · November 28, 2011, 9:36am

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.

Topic		Replies	Views
Problem with select query Databases	5	659	November 17, 2011
This is a question about the best approach to tackle my present problem Databases	31	1862	October 1, 2010
Need help with complex MySQL SELECT Databases	24	1546	December 16, 2010
How to extract month and year values from database column and compare it to current month and date Databases sql , php	40	5985	October 26, 2022
Trouble comparing dates in two tables Databases	30	1740	May 14, 2011

Problem with Update query

Related topics