mysql将多行查询结果插入表 [英] mysql insert multi row query result into table
本文介绍了mysql将多行查询结果插入表的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我遇到了一种情况,我需要使用从另一个查询中获得的数据来升级"表.我要添加缺失值,因此需要插入,但似乎无法正确处理.
I came across a scenario where I need to "upgrade" a table with data I obtain from another query. I am adding missing values so I will need to insert, but I cant seem to get it right.
目标表如下
CREATE TABLE `documentcounters` (
`UID` int,
`DataChar`,
`SeqNum` ,
`LastSignature`,
`DocumentType`,
`SalesTerminal`,
`Active`,
PRIMARY KEY (`UID`)
) ENGINE=InnoDB
我正在尝试做
INSERT INTO documentcounters
SELECT Q1.in_headers, -1,NULL, 17,0,0 FROM
(SELECT DISTINCT(DocumentSeries) as in_headers FROM transactionsheaders )AS Q1
LEFT JOIN
(SELECT DISTINCT(DataChar) as in_counters FROM documentcounters)AS Q2
ON Q1.in_headers=Q2.in_counters WHERE Q2.in_counters IS NULL;
我遗漏了UID,因为我想创建插入语句,但是我得到了列数不匹配"的信息,这很有道理(该死!)
I left UID out because I want the insert statement to create it, but I get a "Column count doesn't match" which makes sense (darn!)
做类似的事情
INSERT INTO `documentcounters`
(`DataChar`,`SeqNum`,`LastSignature`,`DocumentType`,`SalesTerminal`,`Active`)
VALUES
(
(SELECT Q1.in_headers FROM
(SELECT DISTINCT(DocumentSeries) as in_headers FROM transactionsheaders )AS Q1
LEFT JOIN
(SELECT DISTINCT(DataChar) as in_counters FROM documentcounters)AS Q2
ON Q1.in_headers=Q2.in_counters WHERE Q2.in_counters IS NULL),-1,NULL,17,0,0
);
产生子查询返回多于1行"的错误.
yields a "Subquery returns more than 1 row" error.
有什么想法可以使我工作吗?
Any ideas how I can make this work?
欢呼
推荐答案
INSERT INTO `documentcounters`
(`DataChar`,`SeqNum`,`LastSignature`,`DocumentType`,`SalesTerminal`,`Active`)
SELECT Q1.in_headers, -1,NULL, 17,0,0 FROM
(SELECT DISTINCT(DocumentSeries) as in_headers FROM transactionsheaders )AS Q1
LEFT JOIN
(SELECT DISTINCT(DataChar) as in_counters FROM documentcounters)AS Q2
ON Q1.in_headers=Q2.in_counters WHERE Q2.in_counters IS NULL;
如果将UID
定义为auto_increment
,这将起作用.
This will work if UID
is defined as auto_increment
.
这篇关于mysql将多行查询结果插入表的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文