count with update

[SamiBH]

hello

 

I need help 

 

Tables:

 

users:

id

uploades

files:

owner

file_id

 

 

I need to count (file_id) for every user and update "uploades"

like

user1: 10 uploades

user2: 20 uploades

 

how can i do that in sql

UPDATE users SET uploades = ( SELECT count(file_id )
  from users,files
  where users.id = files.owner)

 

thank you

[ale8oneboy]

This may not be the best way to handle this. But you could try creating a database view of upload counts. Then update your users table from the upload counts view.

 

Example:

 

1) The View:

 

CREATE ALGORITHM = MERGE VIEW v_upload_counts AS SELECT user_id, count(*) AS uploads FROM files GROUP BY user_id

 

2) The Update Query:

 

UPDATE users u JOIN v_upload_counts c ON u.user_id = c.user_id SET u.uploads = c.uploads

 

This is just an idea. Anyone else have a better idea?

[SamiBH]

thank you, that worked with small edit