Actualizar campos de otra Tabla usando UPDATE e INNER JOIN

[Chofis]

Buen día, tengo el siguiente problema con una consulta en SQL, tengo una pequeña base de datos, llamada tienda, la cual se compone de tres tablas

únicamente, las tablas son cliente, prodcuto y nota, nota es la tabla donde tiene las llaves foraneas y se relaciona con las dos tablas anteriores

(cliente y producto).

Este diseño de base de datos lo tengo en tres gestores diferentes, los cuales son SQL Server, PostgreSQL y Mysql Workbench.

El código de las tablas para SQL Server es el siguiente:

Código:

CREATE DATABASE tienda;

USE tienda;

CREATE TABLE producto(
	clave_producto INT IDENTITY(1,1),
	nombre_del_producto VARCHAR(20),
	precio FLOAT,
	CONSTRAINT pk_clave_producto PRIMARY KEY(clave_producto)
);

CREATE TABLE cliente(
	clave_cliente INT IDENTITY(1,1),
	nombre_cliente VARCHAR(20),
	CONSTRAINT pk_clave_cliente PRIMARY KEY(clave_cliente)
);

CREATE TABLE nota(
	folio INT IDENTITY(1,1),
	cantidad INT,
	subtotal FLOAT,
	clave_producto_1 INT,
	clave_cliente_1 INT,
	CONSTRAINT pk_folio PRIMARY KEY(folio),
	CONSTRAINT fk_clave_producto_1 FOREIGN KEY(clave_producto_1) REFERENCES producto(clave_producto),
	CONSTRAINT fk_clave_cliente_1 FOREIGN KEY(clave_cliente_1) REFERENCES cliente(clave_cliente)
);

Para el auto increment en las tablas tube que usar la función IDENTITY, ya que AUTO_INCREMENT como en Mysql no funciona en SQL Server ni en

PostgreSQL.

Este es el código para PostgreSQL

Código:

CREATE TABLE producto(
	clave_producto SERIAL,
	nombre_del_producto VARCHAR(20),
	precio FLOAT,
	CONSTRAINT pk_clave_producto PRIMARY KEY(clave_producto)
);

CREATE TABLE cliente(
	clave_cliente SERIAL,
	nombre_cliente VARCHAR(20),
	CONSTRAINT pk_clave_cliente PRIMARY KEY(clave_cliente)
);

CREATE TABLE nota(
	folio SERIAL,
	cantidad INT,
	subtotal FLOAT,
	clave_producto_1 INT,
	clave_cliente_1 INT,
	CONSTRAINT pk_folio PRIMARY KEY(folio),
	CONSTRAINT fk_clave_producto_1 FOREIGN KEY(clave_producto_1) REFERENCES producto(clave_producto),
	CONSTRAINT fk_clave_cliente_1 FOREIGN KEY(clave_cliente_1) REFERENCES cliente(clave_cliente)
);

NOTA: Lo pareceido a auto_increment en este gestor es "SERIAL"

Para MySQL es el siguiente código

Código:

CREATE DATABASE tienda;

USE tienda;

CREATE TABLE producto(
	clave_producto INT AUTO_INCREMENT,
	nombre_del_producto VARCHAR(20),
	precio FLOAT,
	CONSTRAINT pk_clave_producto PRIMARY KEY(clave_producto)
);

CREATE TABLE cliente(
	clave_cliente INT AUTO_INCREMENT,
	nombre_cliente VARCHAR(20),
	CONSTRAINT pk_clave_cliente PRIMARY KEY(clave_cliente)
);

CREATE TABLE nota(
	folio INT AUTO_INCREMENT,
	cantidad INT,
	subtotal FLOAT,
	clave_producto_1 INT,
	clave_cliente_1 INT,
	CONSTRAINT pk_folio PRIMARY KEY(folio),
	CONSTRAINT fk_clave_producto_1 FOREIGN KEY(clave_producto_1) REFERENCES producto(clave_producto),
	CONSTRAINT fk_clave_cliente_1 FOREIGN KEY(clave_cliente_1) REFERENCES cliente(clave_cliente)
);

La estructura de esta pequeña base de datos queda de la siguiente forma:



Despues de tener la estructura de las tablas, se insertan los siguientes registros para las tablas cliente, producto y nota.

Código:

INSERT INTO producto(nombre_del_producto,precio) VALUES('Refresco',15.20);
INSERT INTO producto(nombre_del_producto,precio) VALUES('Papas',9.50);
INSERT INTO producto(nombre_del_producto,precio) VALUES('Cacahuates',13.14);
INSERT INTO producto(nombre_del_producto,precio) VALUES('Pastillas',24);
INSERT INTO producto(nombre_del_producto,precio) VALUES('Chicles',12.50);
INSERT INTO producto(nombre_del_producto,precio) VALUES('Pan',18.90);
INSERT INTO producto(nombre_del_producto,precio) VALUES('Churritos',33.22);
INSERT INTO producto(nombre_del_producto,precio) VALUES('Cerveza',21.50);
INSERT INTO producto(nombre_del_producto,precio) VALUES('Huevo',32);
INSERT INTO producto(nombre_del_producto,precio) VALUES('Jamon',48);
INSERT INTO producto(nombre_del_producto,precio) VALUES('Maruchan',13);
INSERT INTO producto(nombre_del_producto,precio) VALUES('Rastrillo',33.20);
INSERT INTO producto(nombre_del_producto,precio) VALUES('Condon',70);
INSERT INTO producto(nombre_del_producto,precio) VALUES('Tuaya',55.20);
INSERT INTO producto(nombre_del_producto,precio) VALUES('Cotonetes',24.90);
INSERT INTO producto(nombre_del_producto,precio) VALUES('Pasta',32.40);
INSERT INTO producto(nombre_del_producto,precio) VALUES('Helado',16.40);
INSERT INTO producto(nombre_del_producto,precio) VALUES('Suplemento',120.90);
INSERT INTO producto(nombre_del_producto,precio) VALUES('Enjuague',40);
INSERT INTO producto(nombre_del_producto,precio) VALUES('Hilo',18.50);

INSERT INTO cliente(nombre_cliente) VALUES('Ariana Delgado');
INSERT INTO cliente(nombre_cliente) VALUES('Maricela Aguilar');
INSERT INTO cliente(nombre_cliente) VALUES('Edagr Corona');
INSERT INTO cliente(nombre_cliente) VALUES('Doribel Penagos');
INSERT INTO cliente(nombre_cliente) VALUES('Berenice Guerrero');

INSERT INTO nota(cantidad,subtotal,clave_producto_1,clave_cliente_1) VALUES(8,NULL,3,4);
INSERT INTO nota(cantidad,subtotal,clave_producto_1,clave_cliente_1) VALUES(12,NULL,12,2);
INSERT INTO nota(cantidad,subtotal,clave_producto_1,clave_cliente_1) VALUES(18,NULL,17,1);
INSERT INTO nota(cantidad,subtotal,clave_producto_1,clave_cliente_1) VALUES(33,NULL,9,3);
INSERT INTO nota(cantidad,subtotal,clave_producto_1,clave_cliente_1) VALUES(12,NULL,14,1);
INSERT INTO nota(cantidad,subtotal,clave_producto_1,clave_cliente_1) VALUES(22,NULL,8,5);
INSERT INTO nota(cantidad,subtotal,clave_producto_1,clave_cliente_1) VALUES(11,NULL,16,2);
INSERT INTO nota(cantidad,subtotal,clave_producto_1,clave_cliente_1) VALUES(17,NULL,18,3);
INSERT INTO nota(cantidad,subtotal,clave_producto_1,clave_cliente_1) VALUES(21,NULL,19,5);
INSERT INTO nota(cantidad,subtotal,clave_producto_1,clave_cliente_1) VALUES(25,NULL,15,2);

Hasta este punto podemos realizar las correspondientes consultas para visualizar los registros que se agregaron a cada una de las tablas

Código:

SELECT * FROM cliente;

SELECT * FROM producto;

SELECT * FROM nota;

En la tabla nota existe un campo llamado subtotal, el cual hasta el momento no tiene valor alguno almacenado, para obtener el resultado de este campo

en particular se requiere el uso de información de dos campos los cuales son: "cantidad y precio" los cuales se encuentran en tablas diferentes, para

llegar a la solución de este problema se recurrio a los UPDATE e Inner join, ya que nos pide que se actualice el campo subtotal de la tabla nota

usando la información de otra tabla, lo cual nos obliga a usar un join para poder hacer este puenteo de información entre tablas, la consulta que

realice fue la siguiente:

Código:

UPDATE 		nota 
SET 		subtotal = cantidad * precio 
FROM 		nota 
INNER JOIN 	producto ON producto.clave_producto = nota.clave_producto_1;

Consultamos los nuevos registros actualizados: SELEC * FROM nota y vemos en SQL Server que es un éxito la operación:



La cual me funciono de maravilla pero únicamente para SQL Server, para PostgreSQL y MySQL no funciona, use el mismo script para estos gestores y los

mensajes de error que me envia son los siguientes:

ERROR MySQL



ERROR PostgreSQL



Es aqui donde surgen los problemas, ya que este UPDATE que use para SQL Server no funciona en estos dos gestores, así que intente lo siguiente para

ver si podia multiplicar los campos de otra manera y agregarlos al compo correspondiente llamado subtotal de la tabla nota, con el siguiente scirpt:

Código:

SELECT nombre_cliente,nombre_del_producto,subtotal=cantidad*precio FROM
(
SELECT 		nombre_cliente,
			nombre_del_producto,
			cantidad,
			precio,
			subtotal 
FROM 		cliente 
INNER JOIN 	nota 		ON nota.clave_cliente_1 = cliente.clave_cliente 
INNER JOIN 	producto 	ON producto.clave_producto = nota.clave_producto_1
)AS sub;

Con esta consulta me muestra datos (La únion de los campos que se logra con los JOINS) pero en el campo subtotal no me mestra resultado alguno, los

registros de ese campo en particular se muestran vacios.

MySQL



PostgreSQL




Deseo saber cual o cuales son las maneras correctas para poder multiplicar campos de diferentes tablas usando UPDATE y los Joins, intente varias

combinaciones con subconsultas, pero ningúna me ofrece el resultado que requiero.

Espero puedan ayudarme indicandome que debo investigar para poder solucionar este problema, de antemano gracias por leer mi mensaje.

[tonigomila1988]

Hola Chofis,

para la sentencia UPDATE en MySQL tienes que realizar el INNER JOIN en la llamada de la tabla

Código MySQL:

UPDATE tablax AS tx INNER JOIN tablay AS ty
 

y luego el resto sigue con el on, where o using, según como hagas la condición del INNER JOIN (yo evito usar el WHERE en los INNER JOIN, para así distinguir mis propias condiciones con los WHERE)
y luego aplicar el SET.

No se nada de la sintaxis de PostgreSQL, pero espero que la de MySQL te sirva. Un saludo.

[Chofis]

Buena noche tonigomila1988, realice pruebas con el ejemplo que me proporcionas, tanto a la tabla como a las tablas donde se realizara la unión con

JOIN, le asignas un sobrenombre, un alias con "AS" y unicamente llamas los campos de claves primarias para relacionarlos con los campos foraneos a

travez de los sobrenombres, el ejemplo que implemente siguiendo tu modelo de consulta en particular fue la siguiente:

Código SQL:

UPDATE      nota        AS tabla_nota 
INNER JOIN  producto    AS tabla_producto   ON tabla_producto.clave_producto = tabla_nota.clave_producto_1 
INNER JOIN  cliente     AS tabla_cliente    ON tabla_cliente.clave_cliente = tabla_nota.clave_cliente_1 
SET         subtotal = cantidad * precio;
 

El ejemplo que me brindaste funciono y no, en pocas palabras entre en confusión, por que en algunos gestores el script funciono, y en otros no, lo

probe en PHP MyAdmin de APPSERV y funciono, lo probe en un server de prueba y funciono, pero en MySQL Workbench y PostgreSQL no me proporciona

resultados favorables, adjunto la evidencia para que veas que lo que te comento es verdad.

Primero mostrare las capturas de pantalla en donde el script funciono sin problema alguno, y por último mostrare las pantallas en donde me arroja

mensajes de error.


PHP MyAdmin (Funciono correctamente)


Server de prueba (Funciono correctamente)


MySQL WorckBench (No funciono el script)


PostgreSQL (No funcino el script)


Desconosco por que no funciono en PostgreSQL y MySQL WorkBench, tendre que indagar más a detalle por que no funciona esa consulta en particular, me

metere más a la documentación oficial de MySQL para ver si puedo solucionar el problema que me presenta WorkBench y PostgreSQL.

Gracias por tu ayuda, si logro solucionar estos problemas los publicare por este medio, si tienes alguna pista que me puedas brindar para darle

solución a este inconveniente, te lo agradecere bastante.

[vicram10]

proba de la siguiente forma

Código SQL:

UPDATE nota AS tabla_nota 
SET subtotal = cantidad * precio
WHERE EXISTS (SELECT 1 FROM producto WHERE producto.clave_producto = tabla_nota.clave_producto_1)
AND EXISTS (SELECT 1 FROM cliente WHERE cliente.clave_cliente = tabla_nota.clave_cliente_1);
 

[Chofis]

Buen día, gracias "vicram10", he puesto en marcha el script que me proporcionas, pero le agregue una parte a tu script.

Código SQL:

UPDATE nota AS tabla_nota 
    SET subtotal = cantidad * precio
    FROM producto
    WHERE EXISTS (SELECT 1 FROM producto WHERE producto.clave_producto = tabla_nota.clave_producto_1)
    AND EXISTS (SELECT 1 FROM cliente WHERE cliente.clave_cliente = tabla_nota.clave_cliente_1);
 

Después del SET agregue un FROM, y con esto se puede hacer lo que necesitaba, comparto una captura de pantalla del resultado que me arroja la consulta, pero solo me funciono en PostgreSQL, en MySQLWorkbench sigue arrojando error, investigando en la gran biblioteca virtual que es Google, me documente más sobre este tema, y lo que estoy intentando hacer se le conoce con el concepto de "Tabla derivada". Te agradezco tu ayuda y tu tiempo por leer el post y compartir tu conocimiento.

El resultado en PostgreSQL (pgadmin 4)



Resultado en MySQL Workbench




De antemano un cordial saludo.