4

I have a photos table where users can have multiple photos.

I'm trying to create the following query:

$q = Doctrine_Query::create()
   ->update('Photo p')
   ->set('p.photo_type', 1)
   ->where('p.user_id = ?', $id)
   ->andWhere('p.date_added = (SELECT MIN(p.date_added) FROM Photo p WHERE p.user_id = ?)', $id)

The idea is to set the "photo_type" to 1 for that specific photo of this user that has the minimum date added (earliest photo).

I just can't seem to get the syntax right. Can someone point me in the right direction?

Thank you.

EDIT:

It seems I'm trying to do something that can't be done, as per the answer to this question (MySQL Error 1093 - Can't specify target table for update in FROM clause). This question can be closed.

Community
  • 1
  • 1
Tom
  • 30,090
  • 27
  • 90
  • 124
  • Don't you need GROUP BY when using an aggregate function like MIN? Also, you can turn your select table into a join or simply rewrite using either table variables or hash tables – Phil C Feb 12 '11 at 10:58
  • First try putting GROUP BY date_added just before your WHERE clause – Phil C Feb 12 '11 at 10:59

1 Answers1

2

Try this:

$q = Doctrine_Query::create()
   ->update('Photo p')
   ->set('p.photo_type', 1)
   ->where('p.user_id = ?', $id)
   ->orderBy('p.date_added', 'desc')
   ->limit(1);
Sergio
  • 301
  • 1
  • 5
  • Yep, this would have worked... should be ORDER BY ASC, though. DESC will start with the max (latest) date. – Tom Feb 20 '11 at 19:54