How to update database records based on values in another table?

Question

i have USERS TABLE that contains the following fields _id name number_of_posts

and i have POSTS TABLE that contains the following fields _id user_id post_text

no the relationship between the users and posts is one- to -many i.e one user have many post

the question is how to update the number_of_posts in users table such that it will hold the number of post in the posts table

UPDATE

I have these tables filled with about hundreds of records there is no way to use the trigger to update the number_of_posts .

score 2 · Answer 1 · answered Apr 29 '12 at 14:58

Below given sample script shows how to get the number of posts associated with a user by joining both users and posts table using LEFT OUTER JOIN and then uses GROUP BY on the *_id* field in users table to fetch the posts count.

Click here to view the sample in SQL Fiddle.

Query:

CREATE TABLE users 
(
    _id     INT NOT NULL
  , name    VARCHAR(30) NOT NULL
);

CREATE TABLE posts 
(
    _id     INT NOT NULL
  , _userid INT NOT NULL
  , name    VARCHAR(30) NOT NULL
);

INSERT INTO users (_id, name) VALUES
  (1, 'user 1'),
  (2, 'user 2'),
  (3, 'user 3');

INSERT INTO posts (_id, _userid, name) VALUES
  (1, 1, 'post 1'),
  (2, 1, 'post 2'),
  (3, 2, 'post 3');

SELECT          u._id
            ,   u.name
            ,   COUNT(p._id) number_of_posts
FROM            users u
LEFT OUTER JOIN posts p
ON              u._id = p._userid
GROUP BY        u._id;

Output:

_ID NAME    NUMBER_OF_POSTS
--- ------  ---------------
1   user 1         2
2   user 2         1
3   user 3         0

dwurf · Accepted Answer · 2012-04-29T15:21:22.123

You can do this with a trigger on the posts table. Every time a post is inserted, update the number_of_posts on the users table.

See http://dev.mysql.com/doc/refman/5.6/en/triggers.html

Update: full worked solution

drop table if exists users;
create table users (  
   _id bigint unsigned auto_increment primary key,   
   name varchar(50) not null, 
   number_of_posts integer not null default 0
);

drop table if exists posts;
create table posts (  
   _id bigint unsigned auto_increment primary key,   
   user_id bigint unsigned not null, 
   post_text text
);

-- Populate with dummy data
insert into users (name) values ('Bob'), ('Sally');
insert into posts (user_id, post_text) 
    values (1, 'First!!'), (1, 'Second...'), 
           (2, 'Post 1'), (2, 'another post'), (2, 'more posts');

-- One-time update of users table
update users u
set u.number_of_posts = (      
  select count(0) from posts p
  where u._id = p.user_id      
);

-- trigger to keep post count up to date when future posts are made
drop trigger if exists trg_post_count;
delimiter $$

create trigger trg_post_count
after insert on posts
for each row 
begin
    select count(0) into @post_count 
    from posts
    where user_id = NEW.user_id;

    update users
    set number_of_posts = @post_count
    where _id = NEW.user_id;
end;
$$

delimiter ;

-- test trigger
insert into posts (user_id) values (2);
select * from users;

Yes, same result. Some say `count(0)` is cleaner/faster, I don't know if there's any basis for that. Maybe see http://stackoverflow.com/questions/2710621/count-vs-count1-vs-countpk-which-is-better — dwurf, Apr 29 '12 at 22:24

score 1 · Answer 3 · answered Apr 29 '12 at 14:55

1

As a one time update, try this, but the trigger solution is the right one to keep it current:

UPDATE users AS u SET u.number_of posts = (SELECT COUNT(*) FROM posts AS p WHERE p.user_id=u.user_id)

answered Apr 29 '12 at 14:55

GDP

8,109
6
45
82

score 0 · Answer 4 · answered Apr 29 '12 at 14:57

As dwurf suggested, I would definitely recommend putting this in a trigger to keep the field up-to-date. However, I completely disagree with him that it should simply be a number that you increment on each update. That's fragile and error prone. You should use the following query to figure out exactly how many posts each user has:

update
  users
set 
  number_of_posts = ( 
    select
      count(0)
    from
      posts p
    where
      p.user_id = users.id
    )   
;

How to update database records based on values in another table?

4 Answers4