n3mesis125 Posted November 14, 2008 Share Posted November 14, 2008 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 Quote Link to comment https://forums.phpfreaks.com/topic/132709-solved-mysql-update-problem/ Share on other sites More sharing options...
revraz Posted November 14, 2008 Share Posted November 14, 2008 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' Quote Link to comment https://forums.phpfreaks.com/topic/132709-solved-mysql-update-problem/#findComment-690163 Share on other sites More sharing options...
n3mesis125 Posted November 14, 2008 Author Share Posted November 14, 2008 When I try the above I now get the below error: #1111 - Invalid use of group function Quote Link to comment https://forums.phpfreaks.com/topic/132709-solved-mysql-update-problem/#findComment-690164 Share on other sites More sharing options...
revraz Posted November 14, 2008 Share Posted November 14, 2008 I believe that is because you are using SUM in your clause, and you may have to use HAVING instead. Quote Link to comment https://forums.phpfreaks.com/topic/132709-solved-mysql-update-problem/#findComment-690170 Share on other sites More sharing options...
PFMaBiSmAd Posted November 14, 2008 Share Posted November 14, 2008 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. Quote Link to comment https://forums.phpfreaks.com/topic/132709-solved-mysql-update-problem/#findComment-690177 Share on other sites More sharing options...
n3mesis125 Posted November 14, 2008 Author Share Posted November 14, 2008 Thanks for the help guys, removed SUM and it worked perfectly, this made my reporting job 100x easier n3m. Quote Link to comment https://forums.phpfreaks.com/topic/132709-solved-mysql-update-problem/#findComment-690197 Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.