33 lines
672 B
MySQL
33 lines
672 B
MySQL
|
DELIMITER $$
|
||
|
|
CREATE OR REPLACE DEFINER=`root`@`localhost` PROCEDURE `edi`.`item_freeAdd`()
|
||
|
|
BEGIN
|
||
|
|
/**
|
||
|
|
* Rellena la tabla item_free con los id ausentes en vn.item
|
||
|
|
*
|
||
|
|
*/
|
||
|
|
DECLARE vMaxItem INT;
|
||
|
|
|
||
|
|
SELECT MAX(id)
|
||
|
|
INTO vMaxItem
|
||
|
|
FROM vn.item;
|
||
|
|
|
||
|
|
DROP TEMPORARY TABLE IF EXISTS tmp.itemBusy;
|
||
|
|
CREATE TEMPORARY TABLE tmp.itemBusy
|
||
|
|
(id INT PRIMARY KEY)
|
||
|
|
SELECT i.id
|
||
|
|
FROM vn.item i
|
||
|
|
WHERE i.isFloramondo
|
||
|
|
UNION ALL
|
||
|
|
SELECT ifr.id
|
||
|
|
FROM edi.item_free ifr;
|
||
|
|
|
||
|
|
INSERT INTO edi.item_free(id)
|
||
|
|
SELECT i.id
|
||
|
|
FROM vn.item i
|
||
|
|
LEFT JOIN tmp.itemBusy ib ON ib.id = i.id
|
||
|
|
WHERE i.isFloramondo AND ib.id is null;
|
||
|
|
|
||
|
|
DROP TEMPORARY TABLE IF EXISTS tmp.itemBusy;
|
||
|
|
END$$
|
||
|
|
DELIMITER ;
|