Tuesday 28 May 2013

Mysql Update Statement with Join

Mysql Update Statement with Join

Yes, You can use update statement with mysql join also.

See Below Example

1) Create users Table
CREATE TABLE IF NOT EXISTS `users` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  `address` text,
  PRIMARY KEY (`id`)
);

2) Add records in users table.
INSERT INTO `users` (`id`, `name`, `address`) VALUES
(1, 'name 1', 'address 1'),
(2, 'name 2', 'address 2'),
(3, 'name 3', 'address 3'),
(4, 'name 4', 'address 4'),
(5, 'name 5', 'address 5'),
(6, 'name 6', 'address 6'),
(7, 'name 7', 'address 7'),
(8, 'name 8', 'address 8'),
(9, 'name 9', 'address 9'),
(10, 'name 10', 'address 10'),
(11, 'name 11', 'address 11'),
(12, 'name 12', 'address 12'),
(13, 'name 13', 'address 13'),
(14, 'name 14', 'address 14'),
(15, 'name 15', 'address 15');

3) Create salary Table
CREATE TABLE IF NOT EXISTS `salary` (
  `id` int(11) NOT NULL DEFAULT '0',
  `user_id` int(11) NOT NULL,
  `salary` float(10,2) NOT NULL,
  PRIMARY KEY (`id`)
);

4) Add Record in salary Table
INSERT INTO `salary` (`id`, `user_id`, `salary`) VALUES
(1, 9, 5000.00),
(2, 6, 3500.00);

Requirement : update the user record whose salary is 5000.

Solution with Inner join
update users As u INNER JOIN salary As s on s.user_id=u.id set u.name='my salary is 5000'  where s.salary=5000

Solution with LEFT join
update users As u LEFT JOIN salary As s on s.user_id=u.id set u.name='my salary is 5000'  where s.salary=5000

Solution with Right join
update users As u RIGHT JOIN salary As s on s.user_id=u.id set u.name='my salary is 5000'  where s.salary=5000