Mysql Query - insert new data

Question

I have two tables t and t1. Say that t contains the following data

id  name  a  b   c  o   p
1  'John' 1  1   1  23  5

and t1

id1  a1  b1   c1   o1
1    6   7    2    23 
2    9   3    7    23 
3    25  7    12   2

I am looking to get the table t after some operations as the following example :

id  name  a  b   c  o  
1  'John' 1  1   1  23 
2  'John' 6  7   2  23  
3  'John' 9  3   7  23  

My purpose is insert new columns into t where t1.o1=t.o .

Any idea ? I need to use stored procedure ?

Answer 1

You can insert the data you want from table t1 to table t, using this code:

INSERT INTO t (name, a, b, c, o)
SELECT t.name, t1.a1, t1.b1, t1.c1, t1.o1
FROM t1
INNER JOIN t on t1.o1 = t.o

You didn't say if the id column of table t has the AUTO_INCREMENT option enabled. I suppose this option is enabled.

Answer 2

You can do the following,

insert into t (name,a,b,c,o,p) (select name,a1,b1,c1,o1,p from t1 inner join t on t.o=t1.o1);

Assuming you still need column p in table t.

http://sqlfiddle.com/#!2/efb65/1

just noticed the sequence in the resulting table so updated the answer accordingly, assuming auto increment in t.id http://sqlfiddle.com/#!2/a97c06/1

Otherwise you will need to drop the column first and then insert values.

ALTER TABLE t DROP p;
insert into t (name,a,b,c,o) (select name,a1,b1,c1,o1 from t1 inner join t on t.o=t1.o1);

http://sqlfiddle.com/#!2/ddccc/1

Answer 3

It's hard to tell for sure without seeing the exact table schema for table t, but assuming that id column is an auto_increment column and that p column is NULLable

INSERT INTO t (name, a, b, c, o)
SELECT t.name, t1.a1, t1.b1, t1.c1, t1.o1
  FROM t1 JOIN t 
    ON t1.o1 = t.o;

Here is SQLFiddle demo

Answer 4

You can do like this. SQL Fiddle is here

SELECT @rownum := 0;
    INSERT INTO t
    SELECT t.id+(@rownum := @rownum + 1), t.name, t1.a1, t1.b1, t1.c1, t1.o1, t.p 
    FROM t1
    INNER JOIN t on t1.o1 = t.o;

Answer 5

INSERT INTO tableName
SELECT t.id, t.name, t.a, t.b, t.c, t.o
FROM t
INNER JOIN t1 ON t.o=t1.o1

tableName is the name of the table, in which you want to insert your data. The query under the insert into statement, selects the data you want to insert in your table.

If you need to insert this values to table t, then you should use the following code:

INSERT INTO t (id, name, a, b, c, o)
SELECT t.id, t.name, t.a, t.b, t.c, t.o
FROM t
INNER JOIN t1 ON t.o=t1.o1

Answer 6

This might help you MySQL Insert Where query

INSERT INTO t
SELECT t1.o1 
FROM AnotherTable 
INNER JOIN t on t.o=t1.o1