CODEWITHSUNDEEP
mysqlsql
Let me see
Let me see

Reputation: 5094

Create a table in Mysql from selected columns of exisiting table and with same structure

I am a newbie and i am using mysql. I want to create a table product from an existing table customer but i want only the selected columns of customer table to be created in new table and that too with the same structure. Eg In customer table there is a column with name DATE which has default as CURRENT_TIMESTAMP and ON UPDATE CURRENT TIMESTAMP . SO i want that this structure is also created in the new table.

I looked other answers but they do not copy the structure. Please help me

Upvotes: 2

Views: 4354

Answers (3)

peterm
peterm

Reputation: 92785

Here is what you can do

  1. use CREATE TABLE new_table LIKE old_table syntax to create a new table with the same structure
  2. use ALTER TABLE new_table DROP COLUMN unnecessary_column syntax to drop unnecessary columns in the new table
  3. use INSERT INTO new_table ... SELECT ... FROM old_table syntax to copy data

Let's say your customer table looks like 

CREATE TABLE customer
(
  id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, 
  name VARCHAR(5), 
  created DATETIME, 
  modified TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  other_column INT
);

And you want to create a new table customer_new but without other_column

CREATE TABLE customer_new LIKE customer;
ALTER TABLE customer_new DROP COLUMN other_column;
INSERT INTO customer_new (id, name, created, modified)
SELECT id, name, created, modified
  FROM customer;

Here is SQLFiddle demo

Upvotes: 4

Bibin Mathew
Bibin Mathew

Reputation: 26

Try this :

create table product(DATE CURRENT_TIMESTAMP,ON UPDATE CURRENT TIMESTAMP );

INSERT INTO product (DATE,ON) SELECT DATE,ON FROM customer ;

OR

CREATE TABLE product  (DATE CURRENT_TIMESTAMP,ON UPDATE CURRENT TIMESTAMP )
       SELECT DATE,ON  FROM customer;

Upvotes: 0

Satya
Satya

Reputation: 8881

USE THIS 

CREATE TABLE NEW-TAB SELECT NAME,DATE FROM OLD-TAB;

Please change COLUMN/TABLE names as per your requirements

Upvotes: 2

Related Questions