Page 1 of 1

Update one row per user

Posted: Tue Jun 24, 2008 8:24 am
by someberry
A user may have many records in a table. What I want to do is increment a column in the table for each users first row--I do not want to update every single row.

Imagine this was the table:

Code: Select all

 
row_id   |  user_id    |  count
---------+-------------+----------
1        |  1          |  10
2        |  2          |  5
3        |  3          |  2
4        |  1          |  4
5        |  1          |  6
6        |  2          |  1
 
For this, I would only want to update rows 1, 2 and 3. I assume I would need a group by of some sort, but after that I am kinda lost.

Re: Update one row per user

Posted: Tue Jun 24, 2008 8:29 am
by John Cartwright

Code: Select all

UPDATE ... ORDER BY id DESC LIMIT 1

Re: Update one row per user

Posted: Tue Jun 24, 2008 8:38 am
by someberry
Jcart wrote:

Code: Select all

UPDATE ... ORDER BY id DESC LIMIT 1
That would only update one record in the update for one person, not once for each user.

Re: Update one row per user

Posted: Tue Jun 24, 2008 8:53 am
by John Cartwright
Oh sorry, didn't see the text under the code.

I'm sure there is a better way to do it, but this can be done with subquery.

Code: Select all

UPDATE foo ... WHERE row_id IN (SELECT MIN(row_id) FROM foo GROUP BY user_id)

Re: Update one row per user

Posted: Tue Jun 24, 2008 9:03 am
by someberry
Jcart wrote:Oh sorry, didn't see the text under the code.

I'm sure there is a better way to do it, but this can be done with subquery.

Code: Select all

UPDATE foo ... WHERE row_id IN (SELECT MIN(row_id) FROM foo GROUP BY user_id)
Yes, I thought about doing a subquery using IN, but thought there might be a more elegant solution. There would likely be thousands of users to update, not just one or two.