[SOLVED] Mysql UPDATE problem

[n3mesis125]

Hey Guys,

 

I'm using the below query and for whatever reason it keeps throwing an error:

 

UPDATE `csit_actioned`
SET `green` = IF(SUM(UNIX_TIMESTAMP(entry_date) - UNIX_TIMESTAMP(rec_date)) <= 194400,1,0),
SET `yellow` = IF(SUM(UNIX_TIMESTAMP(entry_date) - UNIX_TIMESTAMP(rec_date)) BETWEEN '194400' AND '259201',1,0),
SET `red` = IF(SUM(UNIX_TIMESTAMP(entry_date) - UNIX_TIMESTAMP(rec_date)) > 259200,1,0)
WHERE id='294739'

 

The error i get is:

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SET `yellow` = IF(SUM(UNIX_TIMESTAMP(entry_date) - UNIX_TIMESTAMP(rec_date)) BET' at line 3

 

 

PS: Using PHP5 and MYSQL 5.0.67

 

[revraz]

You should only use SET once, the first time.

 

UPDATE `csit_actioned`

SET `green` = IF(SUM(UNIX_TIMESTAMP(entry_date) - UNIX_TIMESTAMP(rec_date)) <= 194400,1,0),

`yellow` = IF(SUM(UNIX_TIMESTAMP(entry_date) - UNIX_TIMESTAMP(rec_date)) BETWEEN '194400' AND '259201',1,0),

`red` = IF(SUM(UNIX_TIMESTAMP(entry_date) - UNIX_TIMESTAMP(rec_date)) > 259200,1,0)

WHERE id='294739'

 

[n3mesis125]

When I try the above I now get the below error:

#1111 - Invalid use of group function

[revraz]

I believe that is because you are using SUM in your clause, and you may have to use HAVING instead.

[PFMaBiSmAd]

To just find the difference between two values, just subtract tham: a - b. SUM() does not actually do SUM(a-b). It is an aggregate function used to sum values in columns/groups.

[n3mesis125]

Thanks for the help guys, removed SUM and it worked perfectly, this made my reporting job 100x easier

 

n3m.