Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I am trying to create a new table new_table from t1 by comparing primary key values of another table t2

ie, 1) created table new_table

  2) gave the query

insert into new_table select a.* from t1 as a join t2 as b on a.primary_key=b.primary_key.

but after the execution i got a message ( 0 rows affected )

please specify the reason for this.

share|improve this question
    
What does a select * (without the insert) output? –  Phil Nov 23 '12 at 12:47
add comment

1 Answer

I'd have to assume that no matching values exist in the tables' respective primary key columns.

mysql> create database testing;
Query OK, 1 row affected (0.02 sec)

mysql> use testing;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed

mysql> create table foo (id int);
Query OK, 0 rows affected (0.00 sec)

mysql> create table bar (id int);
Query OK, 0 rows affected (0.00 sec)

mysql> create table baz (id int);
Query OK, 0 rows affected (0.00 sec)

mysql> insert into foo values (1), (2), (3);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> insert into bar values (4), (5), (6);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> insert into baz select a.* from foo a join bar b on a.id = b.id;
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> insert into bar values (1), (2);
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> insert into baz select a.* from foo a join bar b on a.id = b.id;
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql>
share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.