auto_increment with the newly created column

Question

I am using MySQL, I have a table called transaction_detail

the create table statement is

Create table transaction_detail(
tx_code varchar(25),
acct varchar(25),
ltx varchar(25),
crcy varchar(25));

I want to add a column called line_key with auto_increment by 1 in this. I know a way to create another table similar to this and add a column with auto_increment in that and insert all data from this table to a newly created table, but I am looking for a easier way like altering this table and adding a column with incremented value.

you want to alter the table `transaction_detail` and add a column which increment value by 1 ? — Avinash Babu, Jun 04 '15 at 15:33
yes something like alter table transaction_detail add column like_key INT; this will add a new column with no values I want that column with values from 1 to the value of the last row in the table — mb1987, Jun 04 '15 at 15:36
@mb1987 see the question i linked. Mysql will auto generate the ids for you. — dognose, Jun 04 '15 at 15:37

Deepak Kumar · Answer 1 · 2015-06-04T15:37:50.267

0

This way you can insert from one table to another

INSERT database.tableNameNew SELECT * FROM database.tableNameOld;

This way you can copy table structure

CREATE TABLE database.tableNameNew LIKE database.tableNameOld;

This is your table with auto increament

Create table transaction_detail(
        `line_key` int(11) NOT NULL AUTO_INCREMENT,
        `tx_code` varchar(25),
        `acct` varchar(25),
        `ltx` varchar(25),
        `crcy` varchar(25)
        PRIMARY KEY (`line_key`),
    );

edited Jun 04 '15 at 15:37

answered Jun 04 '15 at 15:33

Deepak Kumar

413
1
4
11

I know this way my friend – mb1987 Jun 04 '15 at 15:34
May b i got you wrong then @mb1987 – Deepak Kumar Jun 04 '15 at 15:39

auto_increment with the newly created column

1 Answers1