PHP Versions Question posted in
You can find a list of all versions here.

Php versions – Is it Possible to use Max() in WHERE portion of an UPDATE

PHP version 7.0
mySql version 5.5

I’m trying to perform an UPDATE on an existing record. I search for the max(DateTime) to get the last record. DateTime is the key field and is of the type DateTime. There is one field I wish to update when I find that record (email_sent).

My code:

    UPDATE $mysqldb SET email_sent='1', WHERE (DateTime=SELECT max(DateTime) FROM $mysqldb)

$mysqldb is already defined prior. Code will not run in console, gives 1064 error near “WHERE…”. I’ve tried grouping several different ways with parentheses on the WHERE portion…no joy. I’m sure it’s something simple (I hope!). I’ve searched for a couple of hours now trying to find an example and reading docs. Any help appreciated!

Tags:

2

Answers


  1. 0

    I believe the problem is the comma before your WHERE clause. Also, while I have no experience with MySQL, in Oracle I believe you would need to put the “SELECT MAX(…)” part between parentheses. Finally, it would not hurt to use table aliases so that it’s easier to tell each context apart. Try this:

    UPDATE $mysqldb t1 SET t1.email_sent='1' WHERE (t1.DateTime=(SELECT max(t2.DateTime) FROM $mysqldb t2))
    Login or Signup to reply.
  2. 0

    MySql does not allow the use of a subquery that directly references the table to be updated.
    You must enclose the subquery inside another one:

    UPDATE $mysqldb 
SET email_sent='1' 
WHERE DateTime= (SELECT t.DateTime FROM (SELECT max(DateTime) DateTime FROM $mysqldb) t)

    Another option with ORDER BY clause if there is only 1 row to update:

    UPDATE $mysqldb 
SET email_sent='1' 
ORDER BY DateTime DESC
LIMIT 1
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search