mysql modify select result with insert

Question

I am using insert into table2 select from table1 statement.

table2 has few more fields than table1. How can I populate the extra fields with other values in addition to the ones from select result.

table1 has col1, col2, col3 table2 has col1, col2, col3, col4, col5, col6

col4 and col5 in table2 will use modified values from col2 and col3 .

example:

tabl1.col2 has value tag 6512 and tabl1.col3 has value bin location

col4 in table2 will use partial value bin from table1.col3 and "6512" from table1.col2
so table2.col4 will be bin 6512

sql:

insert into table2(col1, col2, col3, col4, col5) select * from table1 (and add values for col4 and col5) 

Any help is appreciated.

Answer 1

Ok the answer can be broken down into 2 part I think.

1. How do I generate 2 extra columns in my sub select.
2. How can I manipulate data in some of the columns of table1 before I place the result of said manipulation into table2

Point 1:

In your sub select you can generate the 3 extra columns as simply as this.

INSERT INTO table2 (col1, col2, col3, col4, col5) 
            SELECT *, 'dummy1', 'dummy2' FROM table1

This of course just demonstrates that you can invent data for the 2 missing columns.

Point2:

So now you need to read the MySQL Manual - String functions section ( I am not coding this for you )

In here you will see that you can instead of just hard coding data for the missing columns, use the values of any column from table1 and pick bits out of it and concatenate bits together.

Look for SUBSTRING and INSTR and so on. This will allow you to build the missing fields from the existing columns.

Test the sub select clause seperately until it produces the required output.