Update one row per user

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
someberry
Forum Contributor
Posts: 172
Joined: Mon Apr 11, 2005 5:16 am

Update one row per user

Post 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.
User avatar
John Cartwright
Site Admin
Posts: 11470
Joined: Tue Dec 23, 2003 2:10 am
Location: Toronto
Contact:

Re: Update one row per user

Post by John Cartwright »

Code: Select all

UPDATE ... ORDER BY id DESC LIMIT 1
someberry
Forum Contributor
Posts: 172
Joined: Mon Apr 11, 2005 5:16 am

Re: Update one row per user

Post 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.
User avatar
John Cartwright
Site Admin
Posts: 11470
Joined: Tue Dec 23, 2003 2:10 am
Location: Toronto
Contact:

Re: Update one row per user

Post 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)
someberry
Forum Contributor
Posts: 172
Joined: Mon Apr 11, 2005 5:16 am

Re: Update one row per user

Post 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.
Post Reply