Mysql – Not able to create a new table new_table from t1 by comparing primary key values of another table t2 in MYSQL

MySQLmysql-5.5

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.

Best 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>

Related Solutions

Mysql – find and insert row to another table using thesql trigger

This should do the trick for you:

DELIMITER $$
DROP TRIGGER IF EXISTS `employee_INSERT` $$
CREATE TRIGGER `employee_INSERT` 
AFTER INSERT ON `employee`
FOR EACH ROW
BEGIN
    INSERT INTO employee_tools (Id, Tool)
    SELECT new.Id, tools.Tool_Name 
            FROM tools
            WHERE tools.Division = new.Division;
END $$
DELIMITER ;

Mysql – Changing primary key from varchar to int

You do not have to make another table if you are changing the primary key.

The steps I would carry out are:

Add the new integer column that will become the primary key.
Populate it with unique values and make sure you have a unique constraint on it. (It must be unique to be certain it can become primary key)
In each child table add an integer column for the foreign key.
Populate the new foreign key by looking up the old foreign key.

e.g.

tblUser (
 oldID varchar(10),
 newID int, -- The new column added, I'm assuming at this point you have the column populated
 ...
)

tblUserLogin (
 keyUser_oldID varchar(10),
 keyNewID int NULL,
 ...
)

Run the following... I'm work mostly with SQL Server these days, so hopefully this pseudocode gives the right idea. The principle applies to any major table-based database system.

UPDATE tblUserLogin
SET keyNewID = (SELECT newID FROM tblUser WHERE oldID = keyUser_oldID

Add the foreign key between the two new columns.
Make sure that the new primary key is set to auto increment.
Once oldID and keyUser_oldID are no longer referenced anywhere you can drop those columns. But only after you are completely certain!

A few things to consider

That's the general principle, a few things to consider.

This works in simple cases and assumes you can stop people using the database for a while and that any code that uses these tables is ready to be switched to use the new column names.
Subqueries are not the most efficient way, but this is a one-off task and unless you have millions of rows and limited time to make the switch it should be fine.
You didn't say what you are storing in the varchar field? If it is a number (but in varchar format) you could always do UPDATE tblSomeTable SET keyNewID = CAST(keyOldID as int) which would be faster and easier.

Most importantly though...

..you don't mention if the database is used for anything important. Even if it isn't:

Back it up beforehand if you haven't already. If you make a mistake in an update statement you can always reference a backup copy.
Think the steps through, these are structural changes, 30 minutes spent planning could save hours of time later on.
Have a backup plan in case something goes wrong!