I have a database my_db and in it two tables t1 and t2.
t1 has two columns ID and count. ID is a list of some integers, say 1,3,4,6,7,8,9 and count is all 0s by default.
t2 also has two columns, ID2 which has a list of integers which are same as that of ID in t1. But, it is possible that they may repeat or may not be present. The second column contains some value, that isn't of much importance to this question. Clarification: ID2 can be 1,1,3,4,3,1,9,8,7,7,7.
Now, what I need to do is for every ID in t1 I need to fill in count, i.e., the number of occurrences of ID as ID2 in t2.
Running a loop through all the values in ID2 and incrementing by 1 every time in corresponding count ought to do it. But I'm unable to code it up, being new to php and sql. I can work in one table. How to work across multiple?
Maybe you can try MySQL update join?
UPDATE t1
LEFT JOIN (SELECT id2,
Count(1) AS num
FROM t2
GROUP BY id2) ref
ON t1.id = ref.id2
SET t1.count = ref.num
Please correct me if I'm wrong.
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments