Sunday 14 October 2012

Mysql Update Syntax - Update Multiple Records

Mysql Update Syntax - Update Multiple Records

How to create a new 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 ;


Following are different ways to update table.
update `users` set `company`='php tutorial';
update `users` set `company`='php tutorial',`name` ='myname'
update `users` set `company`='php tutorial',`name` ='myname' where id>100


You can also update multiple table in single query.
UPDATE users,profile SET users.company='php-tutorial-php' WHERE profile.id=users.id and profile.name='php';