mysql—在一条sql语句中插入多个不“预先知道”的值

kiz8lqtg 于 2021-06-20 发布在 Mysql

关注(0)|答案(2)|浏览(349)

我想创建一个存储过程，用另一个表中的值将多个值插入到一个表中。我很头疼如何创建一个mysql语句来帮助实现这一点。

CREATE PROCEDURE `sp_loadIDs`()

BEGIN
declare vUserID int;

-- I know this should only return one value but dont know the work around for getting all values meeting this criterion
set vUserID = (select UserID from usertbl where DesignationID=1); 
insert into tasktbl (UserID,DefaultUser) values (vUserID,'1');

-- For known values, I know it is as easy as follows:
insert into tasktbl (UserID,DefaultUser) values ('21','1'),('22','1'),('23','1');
END

mysql

来源：https://stackoverflow.com/questions/52201218/inserting-multiple-values-that-are-not-pre-known-in-a-single-sql-statement

2条答案

按热度按时间

nlejzf6q1#

如果我理解正确的话，我想这正是你想要的

INSERT INTO tasktbl
            (UserId,
             DefaultUser)
(SELECT userid,
        '1' AS DefaultUser
 FROM   usertbl
 WHERE  DesignationID = 1);

编辑，跟随op的评论：

INSERT INTO tasktbl
            (UserId)
(SELECT userid
 FROM   usertbl
 WHERE  DesignationID = 1);

赞(0）回复(0）举报 2021-06-20

62lalag42#

也许你可以试试“插入到。。。选择。。。从……，下面是一个例子

INSERT INTO tasktbl (UserID,DefaultUser)
SELECT UserID, DesignationID
FROM usertbl
WHERE DesignationID = 1

赞(0）回复(0）举报 2021-06-20

我来回答

mysql—在一条sql语句中插入多个不“预先知道”的值

2条答案

相关问题

热门标签

最新问答