[SOLUCIONADO] Update en campo con valores de la misma tabla

[Macas]

Muy buenas,

Estoy intentando hacer esta consulta :

Código MySQL:

update cliente 
set IdExtension=(Select distinct groups
            from extension as EX, cliente as CL
            where CL.tipo=EX.file_type)
 

El problema viene (no lo sabia), que no se pueden hace updates en el cual utilices una consulta que hace referencia a la misma tabla.

Alguna idea de como solucionarlo?

He pensado en usar una tabla auxiliar pero no me convence

[gnzsoloyo]

Tablas temporales, pero la única forma es hacerlo en dos pasos:

Código MySQL:

CREATE TEMPORARY tempgrupo
SELECT DISTINCT groups, CL.tipo
FROM extension EX inner join cliente CL on CL.tipo=EX.file_type;
 
UPDATE cliente CL, tempgrupo T
SET CL.IdExtension = T.groups
WHERE CL.tipo = T.tipo;
 

Pero en realidad, si lo miras bien, en la subconsulta estás cruzando las mismas tablas, que de alguna forma ya están relacionadas.
¿Por que necesitas un UPDATE para actualizar una de ellas con datos de la otra, si esa relación ya existe, y ese dato se puede colectar en los SELECTs futuros?
Estas creando duplicidad de datos aparentemente innecesaria.
¿Para qué?

[Macas]

Mira esto es lo que quiero hacer.



Uploaded with ImageShack.us

Es decir que me actualize en campo idextension con el numero de grupo, cuando extensiontype y filetype sean iguales.

Es decir que me ponga un 0 si es videos, un 1 presentaciones y un 3 documentos.

Llevo desde ayer dandole vueltas y no lo consigo

Merci

[gnzsoloyo]

La explicación de la solucion ya te la di.
Lo que quieres hacer ya lo había entendido, lo que no veo es la necesidad, porque la relacion entre los datos ya existe, y lo que propones generará redundancia innecesaria.
Sigo sin ver por que se puede requerir.

[Macas]

Porque son tablas distintas, una es leida de un excel y la otra es de otro sitio, esta otra es la que tengo que actualizar.

No acabo de entender tu solución, creas la tabla y haces un join, pero esa tabla esta vacia no?

Ese join exactamente que es lo que hace, perdon pero no lo veo.

Saludos

[gnzsoloyo]

El uso de tablas temporales, incluyendo las TEMPORARY, que son un caso específico, está claramente explicado en el manual de referencia, a donde te remito para que las entiendas (http://dev.mysql.com/doc/refman/5.0/...ate-table.html).
La lógica es simple: Creas una tabla temporal donde recolectas los casos que vas a actualizar, con el mismo JOIN usado en la subconsulta (y pongo un JOIN explícito y no la basura implícita, porque es más efectivo y de mejor performance). De ese modo salteas la restricción sin necesidad de violar nada.
Por otro lado, como ya te dije, es una tarea innecesaria, porque el dato ya existe, y la tabla, aunque la hayas leído desde un Excel, debe estar forzosamente en una tabla física real de MySQL para intentar hacer cualquier operación en él.
de todos modos, hay un detalle adicional: Nunca, jamás, se cargan datos en una tabla fija de una base de datos, sin haberlos previamente depurado, y eso no lo estás haciendo...
¿A q ué me refiero?
A que si esa tabla se origina en un archivo de Excel, antes de pasarlo a MySQL deberías haber hecho la normalización de datos, insertando una columna dinámicamente, donde insertaras la FK que le correspondería por el valor de esa columna, el de la FK a que hará referencia, y eliminado luego esa columna de cadena de texto. Eso es lo que se hace en todos los casos, y es responsabilidad del desarrollador realizar esos procesos.
Yo he tenido múltiples veces casos semejantes, e incluso algunos donde de una tabla de Excel, o un archivo CSV, luego de depurarlo, obtenía los registros de 17 tablas diferentes. No es algo difícil de hacer, aunque lleva tiempo preparar la lógica.
Lo que no debes hcer, a menos que sea absolutamente necesario, es subir una tabla "cruda" a la base y luego pretender "normalizarla". Eso no se hace. En todo caso, como te propongo, lo que haces es crear tablas temporales donde realizar el proceso de normalización.

En cuanto a lo de redundancia innecesaria, me refiero a que no tiene ningún sentido poner en la tabla "clientes", el ID numérico del tipo de texto, si ya tienes la descripcion del mismo. Estás, en definitiva, repitiendo datos y destruyendo la estructura normalizada que debería existir en esa tabla.
¿Se entiende un poco mejor?

[Macas]

Creo que te he entendido aunque me falta mucha mucha practica.
Ahora otra cosa que diferencia hay entre crear una tabla temporal y otra fisica, rendimiento?

Otra cosa, las tablas temporales que se quedan en memoria? Como se pueden borrar?

Gracias por todo

[gnzsoloyo]

Ahora otra cosa que diferencia hay entre crear una tabla temporal y otra fisica, rendimiento?

Para el uso que necesitas, no generará impacto a menos que estemos hablando de cientos de miles de registros en cada operación. Y por tu descripción no creo que sea el caso.

Otra cosa, las tablas temporales que se quedan en memoria? Como se pueden borrar?

En MySQL existen dos tipos de tablas temporales: Las que se declaran como

Código MySQL:

CREATE TABLE tablaloquesea(... acá el detalle de columnas ...) ENGINE=TEMPORARY;
 

y las que se crean como:

Código MySQL:

CREATE TEMPORARY TABLE tablaloquesea...
 

Las primeras tienen existencia permanente, pero no así su contenido. Cada vez que la conexión donde se creó se cierra, los registros desaparecen, es decir, se vacía completamente. Pero la estructura permanece, vale decir que lo temporal, son los datos.

La segunda es más interesante: Sólo existe mientras dura la conexión, incluyendo sus datos, y se borra físicamente luego de cerrarse. Pero como su entorno de existencia es la conexión a MySQL, el mismo nombre de tabla se puede usar en dos o más conexiones simultáneas sin generar conflicto, ya que la tabla en cuestión se halla sólo en el bloque privado de procesos de esa conexión.

[Macas]

Las tablas si que son muy grandes la verdad, de unos 16 millones de registros.

Y respecto lo de las tablas temporales, la conexión mysql en mi caso no deberia cerrarse nunca por lo que entiendo que las tablas quedan para la properidad .

Se pueden ver y borrar a mano esas tablas?

Gracias por todo

[gnzsoloyo]

Las tablas si que son muy grandes la verdad, de unos 16 millones de registros.

El tema es si las tablas temporales resultan grandes. ¿Cuántos registros entran en ese Excel? Porque que yo sepa, en un Excel no puedes poner 16 millones de registros...

Lo que debes pensar en que debes procesar antes de insertar, por lo que esos datos no deben entrar en la tabla final hasta que no se haya hecho la correspondiente corrección.
Entonces sólo sería necesario considerar los datos entrantes, no los 16 millones que ya están.

Se pueden ver y borrar a mano esas tablas?

No. Las tablas son invisibles para cualquier conexión concurrente, ya te lo dije, y cualquier interfaz que use una conexión propia no las verá.
En otras palabras, sólo podrás "verlas" por el mismo medio que las creas: La conexión donde se ejecutó el create table.

[Macas]

No el excel es muy pequeño, la que es grande es la otra tabla la que tengo que actualizar.

Bueno muchas gracias por todo lo hare como me has dicho

[Macas]

Esto es lo que he echo

Código MySQL:

update ods_eventos
set idextension=
(SELECT groups 
FROM dks_zyncro.aux_ods_eventos_ext_type as ODS, excel_file_extension_group as GR
where ODS.tipo_extension=GR.file_type)
 

Creando antes la tabla aux_ods_eventos.

Claro esto retorna un monton de filas, entonces no me deja hacer el update

[gnzsoloyo]

Bueno, desglosemos mejor el tema, a ver si se entiende cómo hay que hacerlo.
Hasta lo que has descripto, lo que tienes es:
1. Tabla descriptiva de tipos de archivo. Posee un ID y una descripción, ambos únicos en la tabla.
2. Tabla donde se almacena cada archivo subido por un usuario, o algo así. Esta tabla se debería relacionar con la anterior por medio de una FK.
3. Tabla temporal donde se almacenan los datos subidos en un Excel.

En ese contexto, el método propuesto debería funcionar, pero... ¿Cómo relacionas la tabla destino (ods_eventos), con la tabla origen (aux_ods_eventos_ext_type)?
El tema es que para que ese UPDATE funcione, se debe relacionar cada registro de la tabla destino, con un único registro de la tabla origen (o de la subconsulta puesta).
En esencia, le falta un WHERE que determine la relación entre ambas cosas.

¿Podrías postear la estructura real de las tablas que estamos analizando?
Así como lo pones, no es fácil entender la relación de ese esquema de datos. Y sin esa información, es difícil darte una solución correcta.

[Macas]

Te explico,

Hasta ahora tenia 2 tablas que entre ellas no tienen ningún tipo de relación.

1- tabla ods_eventos que tiene muchas columnas, pero los que me interesan son 2 idextension (este es el que tengo que actualizar) y filetype (aqui pueden haber x campos que se pueden repetir, contendrá documento, vídeo, presentación etc)

2- tabla excel_file_extension_type, esta es leida de un excel y solo contiene 2 columnas de 5 filas. Por un lado tiene el grupo (es el campo que extraer y que meter en la otra tabla) contendra 0,1,2,3 y la otra columna file_type, con documento, video, presentacion, esta tabla no se pueden repetir los datos.

Entonces aquí lo que quiero es actualizar el campo idextension de la tabla ods_eventos, es decir si en ods eventos el campo filetype es igual a video, pues que de la tabla excel_file_extension_type el campo grupo (0,1,2,3) correpondiente a video de la columna file_type.

Luego cuando me has comentado tu en el post he creado una tabla auxiliar, pero no temporal (luego la borrare) en la que copio la columna de ods_eventos correpondiente al file_type, la cual contendrá:

documento
documento
video
presentacion
etc....

Luego hago un join entre esta y la de grupo y al lado me quedara:

documento 2
documento 2
video 0
presentacion 1

Entonces lo que me interesa es coger la fila de números y actualizar la tabla ods_eventos, lo que pasa que para hacer un update no puedo hacer una consulta que me devuelva mas de 1 elemento

No se si me he explicado, en la imagen que he puesto se puede ver.

Haber si lo puedo arreglar, un saludo y gracias por la ayuda