contar registros repetidos

[Patriarka]

Hola gente,
como puedo contar registros repetidos en tablas relacionadas?
el ejemplo clasico: usuarios, rol y usuario_rol
quiero saber cuantos usuarios tienen mas de un usuariorol

SELECT * FROM usuario U, rol R, usuariorol UR
AND U.id = UR.usuario_id
AND R.id = UR.rol_id

esto me trae correctamente todos los usuarios con al menos un rol asociado en usuariorol
tengo que contar todos los usuarios que tengas mas de un usuariorol

gracias

[gnzsoloyo]

Código MySQL:

SELECT U.id userId, COUNT(*) roles
FROM usuario U INNER JOIN usuariorol UR ON U.id = UR.usuario_id 
GROUP BY U.id
HAVING COUNT(*) >1
 

[Patriarka]

Perfecto!

lo siguiente es mas compicado.
deberia hacer un update en usuarol sobre estos resultados
algo asi:

Código MySQL:

UPDATE usuariorol
SET delete = "2013-04-04 00:00:00"
WHERE id = 2
-- con los resultados de la query anterior
-- yo me imagino algo asi pero no se que poner
 
IN (    SELECT U.id userId, COUNT(*) roles
    FROM usuario U INNER JOIN usuariorol UR ON U.id = UR.usuario_id
    GROUP BY U.id
    HAVING COUNT(*) >1)
 

gracias por la yuda

[gnzsoloyo]

Primer consejo básico: Nunca pongas como nombre de campo una palabra reservada, y menos una que sea parte de las sentencias principales.
DELETE no puede ser usado como nombre de campo porque dispararía un error de sintaxis.

Otro detalles es que no puedes actualizar la misma tabla que lees. es una restricción básica.

En tu caso, creo que deberás hacerlo en dos pasos, pero puede que haya otra posibilidad. En este momento no puedo hacer las pruebas necesarias. Espera a que alguno más te lo responda.

[zalito12]

Eso te daría fallo, haces un IN con un select de dos campos, prueba con esto:

Código MySQL:

UPDATE usuariorol SET `delete`= "2013-04-04 00:00:00" WHERE id = 2 IN (
        SELECT U.id FROM usuario U INNER JOIN usuariorol UR ON U.id = UR.usuario_id
        GROUP BY U.id HAVING COUNT(*) >1)
 

Prueba primero la select, que te coja lo que quieres, no te vayas a cargar la tabla jeje

[wchiquito]

Hola Patriarka,

No estoy seguro de entender completamente lo que necesitas, sin embargo, una sentencia como la siguiente te puede ser útil:

Código MySQL:

update `usuariorol` `ur`
    inner join (
        select `usuario_id`
        from `usuariorol`
        group by `usuario_id`
        having count(`rol_id`) > 1) `der` on `ur`.`usuario_id` = `der`.`usuario_id`
set `ur`.`delete_at` = now();
 

Un ejemplo completo en sqlfiddle.