970_90

Sunday, 14 October 2012

Replace Table - Update Field, Move Records to another table MySQL

Replace Table - Update Field, Move Records to another table MySQL

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 ;


search for 'arun' in 'arun kumar' and replace with 'pardeep'
select replace('arun kumar','arun','pardeep') 
It will print "pardeep kumar"

search for 'php' in company field and replace with 'php-tutorial-php'
UPDATE users SET company = REPLACE(company, 'php', 'php-tutorial-php');

Replace a table
REPLACE INTO T SELECT * FROM T;

Note:
If you run a replace on existing keys on table "users", and table "profile" references "users" with a forgein key constraint ON DELETE CASCADE,
If you replace table "users"
then table "users" will be updated - but table "profile" will be emptied due to the DELETE before INSERT.


No comments:

Post a Comment