Adding an extra column value with INSERT ... SELECT in MySQL

Question

I have two tables and my goal is to move specific data from the first table into the second table along with a reason for why that data was moved. For instance:

raw_data_table

SELECT * FROM raw_data_table where id IS NULL;

I would want to move this into the second table, which is identical to the first table except for an extra column reason. I tried:

INSERT INTO bad_data_table 
(SELECT * FROM raw_data_table WHERE id IS NULL), "The ID is NULL";

But this returns a syntax error. How can I copy the entire row over and add the reason value?

Cade Roux · Accepted Answer · 2011-04-07 16:47:12Z

33

INSERT INTO bad_data_table 
SELECT *, 'The ID is NULL' AS Reason
FROM raw_data_table
WHERE id IS NULL;

answered Apr 7, 2011 at 16:47

Cade Roux

90.1k42 gold badges186 silver badges268 bronze badges

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

1 Comment

Abe Miessler Over a year ago

I would look at breaking that * into column names as well. Otherwise, when someone ads a new column to the Reason table things will start blowing up.

Khez · Accepted Answer · 2011-04-07 16:50:21Z

0

Try this query:

INSERT INTO bad_data_table VALUES (
(SELECT * FROM raw_data_table WHERE id IS NULL LIMIT 1), "The ID is NULL");

The subquery here needs to have 1 row!

answered Apr 7, 2011 at 16:50

Khez

10.3k2 gold badges35 silver badges53 bronze badges

1 Comment

Kiran Reddy Over a year ago

Error - #1241 - Operand should contain 1 column(s)

Collectives™ on Stack Overflow

Adding an extra column value with INSERT ... SELECT in MySQL

2 Answers 2

1 Comment

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related