在查询脚本
问题描述:
在随后的查询多线响应工作我想这取决于从以前的查询结果多的结果来更新第三个表。在查询脚本
样品展示我的意思
SET @NAME1='name1';
SET @NAME21='name21';
SET @NAME22='name22';
SET @ID1 = (SELECT ID1 FROM TABLE1 WHERE [email protected]);
SET @ID21 = (SELECT ID2 FROM TABLE2 WHERE [email protected]);
SET @ID22 = (SELECT ID2 FROM TABLE2 WHERE [email protected]);
INSERT INTO TABLE3 ('ID1', 'ID2')
VALUES(@ID1, @ID21);
INSERT INTO TABLE3 ('ID1', 'ID2')
VALUES(@ID1, @ID22);
到目前为止那么容易。但是,如果TABLE1返回多行结果,我该如何处理这个问题。 我不得不这样做在MyBatis的“迁移” -script;没有任何计划支持;只是普通的SQL。这个数据库是MySQL。
答
我还没有看到的唯一方法是使用存储过程
DROP PROCEDURE IF EXISTS UpdateWithSuppliedName;
DELIMITER $$
CREATE PROCEDURE UpdateWithSuppliedName(IN aName VARCHAR(20))
BEGIN
DECLARE done INT DEFAULT FALSE;
DECLARE id INT;
DECLARE myCursor CURSOR FOR SELECT t1id FROM table1 WHERE NAME=aName;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
-- Fail if supplied name is not defined
SET @size = (SELECT count(*) from table1 WHERE NAME=aName);
IF @size = 0 THEN
SIGNAL SQLSTATE '45000'
SET MESSAGE_TEXT = "Supplied name does not exist in table1";
END IF;
-- Fail if expected name2 is not there in table2
SET @size = (SELECT count(*) from table2 WHERE NAME="NAME21");
IF @size = 0 THEN
SIGNAL SQLSTATE '45000'
SET MESSAGE_TEXT = "The name 'NAME21' does not exist in table2";
END IF;
-- Fail if expected name2 is not there in table2
SET @size = (SELECT count(*) from table2 WHERE NAME="NAME22");
IF @size = 0 THEN
SIGNAL SQLSTATE '45000'
SET MESSAGE_TEXT = "The name 'NAME22' does not exist in table2";
END IF;
SET @id21 = (SELECT t2id from table2 WHERE NAME="NAME21");
SET @id22 = (SELECT t2id from table2 WHERE NAME="NAME22");
OPEN myCursor;
read_loop: LOOP
FETCH myCursor INTO id;
IF done THEN
LEAVE read_loop;
END IF;
INSERT INTO table3 (t1id, t2id) VALUES (id, @id21);
INSERT INTO table3 (t1id, t2id) VALUES (id, @id22);
END LOOP;
CLOSE myCursor;
END $$
DELIMITER ;
call UpdateWithSuppliedName("NAME11");
-- cleanup
DROP PROCEDURE IF EXISTS UpdateWithSuppliedName;