J blog

Si intentamos borrar registros de una tabla a partir de una select

delete from customers where customers.customerNumber in
(
select customers.customerNumber
from customers
left join payments on customers.customerNumber=payments.customerNumber
group by customers.customerNumber
having count(payments.amount)=0
)

obtenemos el error:
[Err] 1093 – You can’t specify target table ‘customers’ for update in FROM clause

Existen dos maneras de borrar registros de una tabla a partir de una select de la misma tabla:

usando in

delete from customers where customers.customerNumber in
(select customerNumber from (
select customers.customerNumber
from customers
left join payments on customers.customerNumber=payments.customerNumber
group by customers.customerNumber
having count(payments.amount)=0
) as t )

y con inner join

delete customers from customers inner join
(select customerNumber from (
select customers.customerNumber
from customers
left join payments on customers.customerNumber=payments.customerNumber
group by customers.customerNumber
having count(payments.amount)=0
) as t ) tt on customers.customerNumber=tt.customerNumber

es importante decir que tabla borras, pues al hacer el join la sentencia no sabria cual borrar.

Ejercicio

A partir del siguiente enunciado:

Se quiere almacenar información de los empleados, clientes, productos y pedidos de una empresa. De cada cliente queremos almacenar un código único, el nombre y la ciudad. Un cliente puede hacer varios pedidos, pero un pedido es dolo de un cliente. De cada pedido queremos almacenar un código único y la fecha del pedido. Cada pedido lo gestiona un empleado. Un empleado puede gestionar varios pedidos. De cada empleado queremos almacenar un código único, nombre y ciudad. Cada pedido puede incluir varias unidades de un solo producto. De cada producto almacenamos un código único su nombre y precio unitario.

Obtener las siguientes sentencias:

A. muestra un listado con las siguientes columnas: numero_de_pedido,nombre_cliente,nombre_empleado,nombre_producto,cantidad, precio_unitario y total_importe_pedido
B. Muestra un listado con las siguientes columnas: numero_de_pedidos, nombre_cliente, media_importe_pedidos y total_importe_pedidos para cada cliente ordenado por nombre de cliente
C. Muestra un listado con las siguientes columnas: numero_de_pedidos, numero_empleados, ciudad, media_importe_pedidos y total_importe_pedidos para cada ciudad de residencia de los empleados siempre que al menos en la ciudad haya 2 o mas empleados y que en total en la ciudad se hayan firmado al menos 6 pedidos.
D. Muestra todos los datos de los empleados que hayan firmado pedidos a clientes de su misma ciudad.

Solución

El script para crear las tablas seria:

DROP TABLE IF EXISTS `examen_cliente`;
CREATE TABLE `examen_cliente` (
`codigo` int(11) NOT NULL AUTO_INCREMENT,
`nombre` varchar(255) DEFAULT NULL,
`ciudad` varchar(255) DEFAULT NULL,
PRIMARY KEY (`codigo`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

— —————————-
— Table structure for examen_empleado
— —————————-
DROP TABLE IF EXISTS `examen_empleado`;
CREATE TABLE `examen_empleado` (
`codigo` int(11) NOT NULL AUTO_INCREMENT,
`nombre` varchar(255) DEFAULT NULL,
`ciudad` varchar(255) DEFAULT NULL,
PRIMARY KEY (`codigo`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

— —————————-
— Table structure for examen_pedido
— —————————-
DROP TABLE IF EXISTS `examen_pedido`;
CREATE TABLE `examen_pedido` (
`codigo` int(11) NOT NULL AUTO_INCREMENT,
`fecha` date DEFAULT NULL,
`id_cliente` int(11) DEFAULT NULL,
`id_empleado` int(11) DEFAULT NULL,
`id_producto` int(11) DEFAULT NULL,
`cantidad` int(11) DEFAULT NULL,
PRIMARY KEY (`codigo`),
KEY `id_cliente` (`id_cliente`),
KEY `id_empleado` (`id_empleado`),
KEY `id_producto` (`id_producto`),
CONSTRAINT `fk_cliente2` FOREIGN KEY (`id_cliente`) REFERENCES `examen_cliente` (`codigo`),
CONSTRAINT `fk_empleado2` FOREIGN KEY (`id_empleado`) REFERENCES `examen_empleado` (`codigo`),
CONSTRAINT `fk_producto2` FOREIGN KEY (`id_producto`) REFERENCES `examen_producto` (`codigo`)
) ENGINE=InnoDB AUTO_INCREMENT=1014 DEFAULT CHARSET=latin1;

DROP TABLE IF EXISTS `examen_producto`;
CREATE TABLE `examen_producto` (
`codigo` int(11) NOT NULL AUTO_INCREMENT,
`nombre` varchar(255) DEFAULT NULL,
`precio` float DEFAULT NULL,
PRIMARY KEY (`codigo`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

A.
select p.codigo as numero_de_pedido,
c.nombre as nombre_cliente,
pr.nombre as nombre_producto,
p.cantidad,
pr.precio,
p.cantidad*pr.precio as total_importe_pedido
from examen_pedido p
inner join examen_cliente c on p.id_cliente=c.codigo
inner join examen_producto pr on p.id_producto=pr.codigo
inner join examen_empleado e on p.id_empleado=e.codigo

B.
select count(*) as numero_de_pedidos,
c.nombre as nombre_cliente,
avg(p.cantidad*pr.precio) as media_importe_pedidos,
sum(p.cantidad*pr.precio) as total_importe_pedidos
from examen_pedido p
inner join examen_cliente c on p.id_cliente=c.codigo
inner join examen_producto pr on p.id_producto=pr.codigo
inner join examen_empleado e on p.id_empleado=e.codigo
group by c.codigo

C.
select count(p.codigo) as numero_de_pedidos,
count(e.codigo) as numero_empleados,
e.ciudad,
avg(p.cantidad*pr.precio) as media_importe_pedidos,
sum(p.cantidad*pr.precio) as total_importe_pedidos
from examen_pedido p
inner join examen_cliente c on p.id_cliente=c.codigo
inner join examen_producto pr on p.id_producto=pr.codigo
inner join examen_empleado e on p.id_empleado=e.codigo
group by e.ciudad having count(e.codigo)>=2 and sum(p.codigo)>=6

D.
select e.*,p.codigo,p.fecha,c.nombre,c.ciudad
from examen_pedido p
inner join examen_cliente c on p.id_cliente=c.codigo
inner join examen_producto pr on p.id_producto=pr.codigo
inner join examen_empleado e on p.id_empleado=e.codigo
where e.ciudad=c.ciudad

Los check en mysql no funcionan

si creamos una tabla y le añadimos un check

CREATE TABLE cliente (
SD integer CHECK (SD > 0),
nombre varchar (30),
apellido varchar(30)
);

E intentamos introducir un dato que no cumpla el check no nos da ningun error:

INSERT INTO cliente values ('-2','abc','zz');
INSERT INTO cliente values (-3,'abc','zz');

Vemos que en la tabla tenemos los valores.

Para solventar el problema tenemos que recurrir a un trigger

CREATE TRIGGER check_trigger
BEFORE INSERT
ON cliente
FOR EACH ROW
BEGIN
IF NEW.SD<0 THEN
CALL `Error: El valor de SD tiene que ser mayor que 0`;
END IF;
END

Ahora si intentamos introducir un valor que no cumple los requisitos

INSERT INTO cliente values (-4,’tiene que fallar’,’por el trigger’);

Obtenemos un error, tal y como esperabamos:

[Err] 1305 – PROCEDURE clases.Error: El valor de SD tiene que ser mayor que 0 does not exist

Hemos resuelto el problema, no es del todo cierto, pues el trigger era solo para insert, si actualizamos un valor no salta:

update cliente set nombre=’cambiado’ where sd=-2

En mysql no se puede crear un trigger para insert or update como en oracle, por lo que hay que duplicar el trabajo.

CREATE TRIGGER check_trigger_update
BEFORE update
ON cliente
FOR EACH ROW
BEGIN
IF NEW.SD<0 THEN
CALL `Error: El valor de SD tiene que ser mayor que 0`;
END IF;
END

Ahora si ejecutamos la sentencia:

update cliente set sd=-4 where sd=-2

Nos devuelve un error, como era de esperar.