Home > MySQL > Mysql Copy Records With Insert Select Command

Mysql Copy Records With Insert Select Command

mysql_logoThere’s a time you want to copy your MySQL row(s) into another table or on the same table. Here i’ll show you how to do it.

Let say, you have this table (lets name it tbl_persons), id is primary key and auto increment:

Id

Name Age Created
1 Billy 19 2009-11-06
2 Cindy 17 2009-11-06

Now you want to duplicate these two rows, but only the name, you can use insert select command query:

INSERT INTO `tbl_persons` (`Name`, `Age`, `Created`) SELECT `Name`, `Age`, CURDATE() FROM `tbl_persons` WHERE `Created` = '2009-11-06'

What this query to do is select the name that Created on 6th Nov 2009 and copy it into the same table but with different created date (for this example is the current date).

The result:

Id Name Age Created
1 Billy 19 2009-11-06
2 Chindy 17 2009-11-06
3 Billy 19 2009-11-07
4 Chindy 17 2009-11-07

Here’s another example if you want to copy field value into another table. This is the destination table (lets name it…umm… tbl_qualify), just the same with tbl_persons, id field is primary key and auto increment:

id Name
1 Glenn

This is the query:

INSERT INTO `tbl_qualify` (`Name`) SELECT `Name` FROM `tbl_persons` WHERE `age` > 18

The query is used to copy to tbl_qualify, but only the records (persons) that have age above 18.

Here is the final result:

id Name
1 Glenn
2 Billy

Well. That’s it šŸ™‚ Feel free to give me some suggestions and critics.

If you interested see my other post and give me another feedback. Thanks!

Advertisements
Categories: MySQL Tags: , , , , ,
  1. Jay
    December 23, 2009 at 2:11 pm

    hi! but what if you want to copy a record to the same table but you want to modify one or more columns. For example, in this command:
    INSERT INTO `tbl_persons` (`Name`, `Age`, `Created`) SELECT `Name`, `Age`, CURDATE() FROM `tbl_persons` WHERE `Created` = ‘2009-11-06’

    ..but in the new record I want to change the Age to 20..

    Do you know the syntax for this? Thanks and pls email me if you know the solution.

    • December 28, 2009 at 9:48 am

      Here’s the query:
      INSERT INTO `tbl_persons` (`Name`, `Age`, `Created`) SELECT `Name`, `20`, CURDATE() FROM `tbl_persons` WHERE `Created` = ā€˜2009-11-06ā€²
      Notice the ’20’ from the query. It’s the modify field i want to change. Hope this will answer your question

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: