SQL count variables and write into another table

Question

I need to transfer data from karma mod in phpbb to karma in smf.

Phpbb has a table phpbb_karma which is basically a karma log. Important fields we need to focus on are user_id (user which got the karma) and karma_action (positive or negative karma, shown as + or -).

I would need a query to count all the pluses and minuses and then copy the given results for each unique user_id into smf_members table under appropriate id_member (user_id field in smf) and karma_good (all the pluses) and karma_bad (all the minuses).

I think it can be done but I am not SQL expert.

chetan · Accepted Answer · 2013-07-04 10:25:31Z

1

update smf_members a, 
    (select user_id,
    sum(if(karma_action='+',1,0)) karma_good,
    sum(if(karma_action='-',1,0)) karma_bad
    from phpbb_karma
    group by user_id) b
set a.karma_good=b.karma_good, a.karma_bad = b.karma_bad
where a.id_member = b.user_id;

edited Jul 4, 2013 at 10:25

answered Jul 4, 2013 at 10:12

chetan

2,9161 gold badge16 silver badges15 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

WebDeveloper Over a year ago

#1054 - Unknown column 'a.user_id' in 'where clause'

WebDeveloper Over a year ago

user_id is called id_member in destination table

Yannis · Accepted Answer · 2013-07-04 10:12:29Z

0

How about something like this:

    SELECT DISTINCT user_id, (SELECT COUNT(1) as points
    FROM phpbb_karma
    WHERE action = '+' and k.user_id = user_id), 
    (SELECT COUNT(1) as points
    FROM phpbb_karma
    WHERE action = '-' and k.user_id = user_id)
FROM
    phpbb_karma k

Not the most optimized one but given this is an one-off operation then it shouldnt matter much.

answered Jul 4, 2013 at 10:12

Yannis

6,1875 gold badges47 silver badges63 bronze badges

2 Comments

WebDeveloper Over a year ago

#1054 - Unknown column 'user_id' in 'field list' (it is in phpbb_karma for sure)

Yannis Over a year ago

The above test works fine locally in my SQL Server database. Unfortunatelly I dont have mysql.

Collectives™ on Stack Overflow

SQL count variables and write into another table

2 Answers 2

2 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related