Menu
I'm trying to insert additional rows into a table which requires a value to be retrieved from another table. Below is an example query:
insert into a.grades (rollno, grade)
values(select rollno from b.students where ssn=12345, 'A');
Structure of b.students table is rollno, ssn, name.
I knew the above query is wrong. Is there a way to retrieve 1 value from other table while inserting a row?
404
The MySQL INSERT INTO SELECT StatementThe INSERT INTO SELECT statement copies data from one table and inserts it into another table. The INSERT INTO SELECT statement requires that the data types in source and target tables matches. Note: The existing records in the target table are unaffected.
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.
MySQL INSERT INTO – General SyntaxINSERT INTO table_name(column_1,column_2,column_3) VALUES (value_1,value_2,value_3); In the INSERT INTO query, you should specify the following information: table_name : A MySQL table to which you want to add a new row.
INSERT INTO SELECT Statement Syntax We can insert data from other SQL tables into a table with the following INSERT INTO SELECT statement. This query performs the following tasks: It first Selects records from a table ( Select statement) Next, it inserts into a table specified with INSERT INTO.
INSERT INTO a.grades (rollno, grade)
SELECT rollno, 'A' FROM b.students WHERE ssn = 12345;
Some DBMS would accept the following, with an extra set of parenthesis around the SELECT statement:
INSERT INTO a.grades (rollno, grade)
VALUES((SELECT rollno FROM b.students WHERE ssn = 12345), 'A');
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
