970_90

Sunday, 14 October 2012

MySQL Delete Table Example

MySQL Delete Table Example

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;


Delete record(s) from table
DELETE FROM users WHERE id=5;
It will delete a record where id is 5

Delete record from two table when they are in join.
DELETE users1,users2 FROM users1,users2,users3 WHERE tusers1.id=users2.id AND users2.id=users3.id


Keep in Mind following points..


  • If we specify the LOW_PRIORITY keyword in query, execution of the DELETE is delayed until no other clients are reading from the table.
  • For MyISAM tables, if we specify the QUICK keyword in query, then the storage engine will not merge index leaves during delete, which  will speed up certain kind of delete.
  • If we specify the IGNORE causes MySQL to ignore all errors during the process of deleting rows. 


No comments:

Post a Comment