Mysql can insert data to a specified table based on the query results.

Source: Internet
Author: User

Mysql can insert data to a specified table based on the query results.

MySQL can insert the queried record set to the specified table. The example is as follows:

First, describe the structure of the three tables:

S (sno, sname. sex, age, dept) // student information table

C (cno, cname, tname) // course information table

SC (sno, cno, grade) // optional course information table

Now the problem is:

Insert the student ID, name, and system name of a course score greater than or equal to 80 into an existing (to be created) basic table stu (sno, sname, dept ).

The existing database Schema of the stu table is:

 

<span style="font-size:18px;color:#FF0000;">drop table if EXISTS stu ;create table stu (  sno int(4) unique ,  sname varchar(20),  dept varchar(10));</span>


 

Now write the SQL statement of the result:

 

<span style="font-size:18px;color:#FF0000;">insert into stu (sno,sname,dept)select sno ,sname , dept from s wheres.sno in (select sc.sno from sc where sc.grade >= 80 );</span>

 

The following are the syntax rules of the preceding SQL statement:

 

Insert into table name 1 (Field List 1) SELECT Field List 2 FROM table name 2 WHERE condition expression;
Table Name 1: indicates the table to which the record is inserted. Field List 1: indicates which fields are assigned values. Field List 2: The data of the fields queried from the table. Table name 2: indicates the table from which the record is queried. Conditional expression: Specifies the query conditions for SELECT statements.

 

After the records queried in "table name 2" are inserted into "table name 1", the original records are still saved in "table name 2.

Tip:

When using this method, you must ensure that the number of fields in "Field List 1" and "Field List 2" is the same, make sure that the data type of each corresponding field is the same. If the data types are different, the database system reports an error. Then, the INSERT statement is blocked from being executed downward.


Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.