mysql插入多行结果集,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.

The destination table is the following

CREATE TABLE `documentcounters` (

`UID` int,

`DataChar`,

`SeqNum` ,

`LastSignature`,

`DocumentType`,

`SalesTerminal`,

`Active`,

PRIMARY KEY (`UID`)

) ENGINE=InnoDB

and I am trying to do something like

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;

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!)

Doing something like

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

);

yields a "Subquery returns more than 1 row" error.

Any ideas how I can make this work?

Cheers

解决方案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;

This will work if UID is defined as auto_increment.