Menu
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?
644
You can use a select-statement within an INSERT statement to insert zero, one, or more rows into a table from the result table of the select-statement. The select-statement embedded in the INSERT statement is no different from the select-statement you use to retrieve data.
The basic syntax for adding a new column is as follows: ALTER TABLE table_name ADD column_name data_type constraints; The SQL ALTER TABLE add column statement we have written above takes four arguments.
The syntax for assigning a value to a SQL variable within a SELECT query is @ var_name := value , where var_name is the variable name and value is a value that you're retrieving. The variable may be used in subsequent queries wherever an expression is allowed, such as in a WHERE clause or in an INSERT statement.
INSERT INTO bad_data_table
SELECT *, 'The ID is NULL' AS Reason
FROM raw_data_table
WHERE id IS NULL;
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
