Archive

Posts Tagged ‘table’

Mysql Copy Records With Insert Select Command

November 7, 2009 2 comments

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: , , , , ,