970_90

Sunday, 14 October 2012

mysql insert syntax - Add records from another table - Copy Table from one table to another

mysql insert syntax - Add records from another table - Copy Table from one table to another

Create a table
CREATE TABLE IF NOT EXISTS `users` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(200) DEFAULT NULL,
  `image` varchar(255) DEFAULT NULL,
  `company` varchar(150) DEFAULT NULL,
  `created` datetime DEFAULT NULL,
  `modified` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;


Insert record - Way 1
insert into `users` set name='php tutorial'


Insert record - Way 2
INSERT INTO `users` (`id`, `name`, `image`, `company`, `created`, `modified`) VALUES (NULL, 'arun', NULL, 'php-tutorial', NULL, CURRENT_TIMESTAMP);

Add multiple records
INSERT INTO `mysql_certification`.`users` (`id`, `name`, `image`, `company`, `created`, `modified`) VALUES (NULL, 'arun', NULL, 'php-tutorial', NULL, CURRENT_TIMESTAMP), (NULL, 'arun', NULL, 'php-tutorial', NULL, CURRENT_TIMESTAMP);

Copying rows from one table to another
INSERT INTO `users`  (name, company) select name, company from users where id<5

1 comment:

  1. Really like that you are providing such information on PHP MYSQl with JAVASCRIPT ,being enrolled at http://www.wiziq.com/course/5871-php-mysql-with-basic-javascript-integrated-course i really thank you for providing such information it was helpful.

    ReplyDelete