mysql insert multi row query result into table

Question

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

if you run the sub queries separate do they only return 1 record? — Robert
– Robert, Commented Jan 17, 2012 at 16:03

ypercubeᵀᴹ · Accepted Answer · 2012-01-17 16:10:52Z

2

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.

edited Jan 17, 2012 at 16:10

answered Jan 17, 2012 at 16:03

ypercubeᵀᴹ

116k19 gold badges181 silver badges248 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

André Moreira Over a year ago

just remove Values and that's it?!

Joe Stefanelli · Accepted Answer · 2012-01-17 16:04:27Z

1

If you want the INSERT to create the UID values, then UID must be defined as an auto-incrementing column.

CREATE TABLE `documentcounters` (
  `UID` INT NOT NULL AUTO_INCREMENT,
   ...

answered Jan 17, 2012 at 16:04

Joe Stefanelli

136k21 gold badges243 silver badges241 bronze badges

Collectives™ on Stack Overflow

mysql insert multi row query result into table

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related