Cómo comprobar que un campo de una tabla exista en otra tabla?

[ldp]

Hola a todos:

Tengo una BD y en una tabla están guardados los usuarios y en la otras sus mensajes. Yo quiero borrar los mensajes de los usuarios que ya no existen con una consulta directamente en mysql. Se puede hacer?

La estructura es:

Tabla usuarios: ID_user - Nombre_usuario - Email
Tabla mensajes: ID_msj - Título - Mensaje - ID_user

[gnzsoloyo]

Algo así debería funcionar. Sino, la única forma sería con dos consultas:

Código MySQL:

DELETE FROM mensajes 
WHERE ID_user IN
   (SELECT U.ID_user FROM usuarios U LEFT JOIN mensajes M ON U.ID_user = M.ID_user
    WHERE M.ID_user IS NULL);
 

[ldp]

Gracias por responder tan rápido ;) Lo estoy haciendo directamente en phpmyadmin y no me funciona, pero es por cuestión de sintaxis, podrías ponérmelo para usarlo en phpmyadmin?

EDITO: quiero hacer primero un SELECT antes de borrar nada para ver lo que me devuelve, si lo pongo así me devuelve valor vacío:

Código MySQL:

SELECT * FROM `mensaje` WHERE `mensaje`.`uid` IN 
(SELECT `usuarios`.`uid` FROM `usuarios` 
LEFT JOIN `mensajes` ON `usuarios`.`uid` = `mensajes`.`uid` WHERE `mensajes`.`uid` IS NULL)
 

[gnzsoloyo]

SI lo quieres hacer con dos consultas, esto sería más simple, pero requiere que no se cierre la conexión mientras se ejecutan las dos (es preferible hacerlo en un stored procedure):

Código MySQL:

CREATE TEMPORARY TABLE msj_temp
SELECT U.ID_user 
FROM usuarios U LEFT JOIN mensajes M ON U.ID_user = M.ID_user
WHERE M.ID_user IS NULL;
 
DELETE FROM mensajes 
WHERE ID_user IN
   (SELECT U.ID_user FROM msj_temp);
 

La tabla creada desaparecerá cuando se reinicie la conexión;

[ldp]

Antes de nada muchísimas gracias por ayudarme ;)

He probado haciendo una tabla fija en vez de temporal para ver si funciona y como me lo has puesto me da de resultado los usuarios que no han escrito ningún mensaje, en vez de los mensajes que no tienen usuario.

Lo he puesto así:

Código MySQL:

CREATE TEMPORARY TABLE msj_temp
SELECT M.ID_mensaje
FROM mensajes M LEFT JOIN usuarios U ON M.ID_user = U.ID_user
WHERE U.ID_user IS NULL;
 
DELETE FROM mensajes
WHERE ID_mensaje IN
(SELECT M.ID_mensaje FROM msj_temp);
 

Sólo he ejecutado la primera parte (el SELECT), esto me da una tabla con los ID de los mensajes "sin dueño". Me gustaría que me dijeras si la segunda parte está bien. En teoría coge los ID de los mensajes sin dueño de la tabla temporal y los que coinciden en la tabla mensajes los borra, está bien?

[gnzsoloyo]

Hay una cosa que me intriga: ¿No has definido ninguna FK en el campo ID_user de la tabla mensajes?
Si estás usando tablas MyISAM, se comprende, porque simplemente no se puede (MyISAM no soporta FK), pero si estás usando tablas InnoDB, deberías, porque de esa forma te aseguras que no existan mensajes sin usuarios.

Como sea, si aún no has definido una Fk que otorgue esa restricción, la solución entonces sería:

Código MySQL:

CREATE TEMPORARY TABLE msj_temp
SELECT ID_msj 
FROM mensajes 
WHERE ID_user IS NULL OR ID_user = '';
 
DELETE FROM mensajes 
WHERE ID_msj IN
   (SELECT ID_msj FROM msj_temp);
 

En el primer caso estoy suponiendo que pueden llegar a ingresarse mensajes con ID_user como vacíos y no como NULL.

[ldp]

El problema es que los mensajes son del foro y he borrado los usuarios inactivos, pero los mensajes quiero que permanezcan. He decidido en vez de borrarlos crear un usuario "admin" y hacer un UPDATE para que los mensajes huérfanos pertenezcan ahora al usuario "admin.

Código MySQL:

UPDATE `mensajes`
SET `id_usuario` = 2
WHERE `id_mensaje` IN
(SELECT `id_mensaje` FROM `msj_temp`)
 

El problema es que la BD me tira:

#1317 - Query execution was interrupted

Estoy investigando a ver cuál es el problema...

EDITO: he probado a hacer sólo con un registro en msj_temp y funciona, el problema es que tengo más de 2000 registros y hacerlo uno por uno es un suicidio. Alguna idea?

[gnzsoloyo]

he probado a hacer sólo con un registro en msj_temp y funciona, el problema es que tengo más de 2000 registros y hacerlo uno por uno es un suicidio. Alguna idea?

Esta idea:

Código MySQL:

UPDATE mensajes 
SET ID_user = 2
WHERE 
    ID_user NOT IN (SELECT ID_user FROM usuarios)
    AND (ID_user IS NULL OR ID_user = '' OR ID_user !=2);
 

Se supone que pondrá 2 en ID_user sin el contenido del campo no está entre los activos, es NULL, es un valor vacío o no es del Admin.

[ldp]

Muchísimas gracias por toda la ayuda que me has dado. Me ha servido de mucho y he conseguido resolver mi problema.

No te doy más karma porque no puedo :D