UPDATE BELOW!
Who can help me out
I have a table:
CREATE TABLE `group_c` (
`parent_id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`child_id` int(11) DEFAULT NULL,
`number` int(11) DEFAULT NULL,
PRIMARY KEY (`parent_id`)
) ENGINE=InnoDB;
INSERT INTO group_c(parent_id,child_id)
VALUES (1,1),(2,2),(3,3),(4,1),(5,4),(6,4),(7,6),(8,1),(9,2),(10,1),(11,1),(12,1),(13,0);
I want to update the number field to 1 for each child that has multiple parents:
SELECT group_concat(parent_id), count(*) as c FROM group_c group by child_id having c>1
Result:
GROUP_CONCAT(PARENT_ID) C
12,11,10,8,1,4 6
9,2 2
6,5 2
So all rows with parent_id 12,11,10,8,1,4,9,2,6,5 should be updated to number =1
I've tried something like:
UPDATE group_c SET number=1 WHERE FIND_IN_SET(parent_id, SELECT pid FROM (select group_concat(parent_id), count(*) as c FROM group_c group by child_id having c>1));
but that is not working.
How can I do this?
SQLFIDDLE: http://sqlfiddle.com/#!2/acb75/5
[edit]
I tried to make the example simple but the real thing is a bit more complicated since I'm grouping by multiple fields. Here is a new fiddle: http://sqlfiddle.com/#!2/7aed0/11
For anyone interested. This is how I solved it. It's in two queries but in my case it's not really an issue.
UPDATE group_c INNER JOIN (
SELECT parent_id, count( * ) AS c
FROM `group_c`
GROUP BY child1,child2
HAVING c >1
) AS cc ON cc.parent_id = group_c.parent_id
SET group_c.number =1 WHERE number =0;
UPDATE group_c INNER JOIN group_c as gc ON
(gc.child1=group_c.child1 AND gc.child2=group_c.child2 AND gc.number=1)
SET group_c.number=1;