CODEWITHSUNDEEP
mysql
Betty Mock
Betty Mock

Reputation: 1393

mysql copy many records with one change

Here is a table Evact:

+--------------+-----------------------+------+-----+---------+-------+
| Field        | Type                  | Null | Key | Default | Extra |
+--------------+-----------------------+------+-----+---------+-------+
| EvActMas     | char(10)              | NO   | PRI |         |       |
| EvActSub     | char(10)              | NO   | PRI |         |       |
| EvActCode    | char(10)              | NO   | PRI |         |       |
| EvActIncOutg | enum('I','O','B','N') | YES  |     | NULL    |       |
| EvActBudAct  | enum('B','A','O')     | YES  |     | NULL    |       |
    ...other columns ...

and here are some records:

EvActMas    EvActSub    EvActCode   EvActIncOutg    EvActBudAct ..other..

Bank-2017   Incoming    mth01       I              A
Bank-2017   Incoming    mth02       I              A
Bank-2017   Incoming    mth03       I              A
Bank-2017   Incoming    mth04       I              A
Bank-2017   Incoming    mth05       I              A
Bank-2017   Incoming    mth06       I              A

I want to add six new records to the table where 'Incoming' is changed to 'Outgoing' and 'I' is changed to 'O'.

I did it the hard way by creating a new table from the old one; updating the new table and then inserting back into Evact:

Create table btemp like Evact;
update btemp set Evact = 'Outgoing', EvActIncOutg = 'O';
insert into Evact select * from btemp;

That worked, but I want to get better at SQL. What I wish for is a way to do this in one step by joining Evact to itself in some way. Does anyone have a suggestion?

Upvotes: 0

Views: 34

Answers (1)

Caius Jard
Caius Jard

Reputation: 74605

If you want to insert a bunch of rows that are part copies of existing rows:

INSERT INTO evact 
SELECT evactmas, 'Outgoing', evactcode, 'O', evactbudact, ...other..
FROM evact

You make a Select statement that is the data you want to insert, some columns in the select are the values as-is, other columns are the new values

If you aren't specifying all the columns in the select you'll have to put a list of column names in brackets after the INTO so MySQL knows which columns are to get what data. You can only omit the columns list if your select query selects the same number of columns in the table (in which case the columns selected must be in the same order as the table columns to be inserted into)

If your table has a calculated primary key (auto increment for example) specify the value to insert as 0 or NULL to have MySQL calculate a new value for it, or name all the columns except that one after the INTO and omit it from the select list

Upvotes: 1

Related Questions