Insert Data from the Same Table

You can insert data into a table based on other data in the same table. Essentially, this means copying all or part of a row.

For example, you can insert a new row in the publishers table that is based on the values in an existing row in the same table. Make sure you follow the rule on the pub_id column:

insert publishers 
select "9999", "test", city, state 
    from publishers 
    where pub_name = "New Age Books"

 (1 row affected) 

select * from publishers

 pub_id  pub_name              city        state 
------- --------------------- -------     ------ 
0736    New Age Books         Boston      MA 
0877    Binnet & Hardley      Washington  DC 
1389    Algodata Infosystems  Berkeley    CA 
9999    test                  Boston      MA 
 
(4 rows affected) 

The example inserts the two constants (“9999” and “test”) and the values from the city and state columns in the row that satisfied the query.