• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 71
  • Last Modified:

MySQL - update table from 2 other tables.

Hi,
Can anyone provide an SQL query to get the distinct `title` from the`locations` table where locations.id = staff_locations.location_id and insert into `locations_2` table?

I hope that makes sense

Thanks in advance.

 CREATE TABLE IF NOT EXISTS `staff_locations` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `staff_id` int(10) unsigned NOT NULL,
  `location_id` int(10) unsigned NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `unique_ids_idx` (`staff_id`,`location_id`),
  KEY `location_id` (`location_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=6 ;



INSERT INTO `staff_locations` (`id`, `staff_id`, `location_id`) VALUES
(1, 1, 200),
(2, 1, 202),
(3, 1, 211),
(4, 2, 211),
(5, 1, 224),
(6, 2, 229);



CREATE TABLE IF NOT EXISTS `locations` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `title` varchar(255) DEFAULT '',
  `position` int(11) NOT NULL DEFAULT '9999',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=231 ;



INSERT INTO `locations` (`id`, `title`, `position`) VALUES
(200, '2209', 9999),
(201, '2210', 9999),
(202, '2211', 9999),
(203, '2212', 9999),
(204, '2213', 9999),
(205, '2214', 9999),
(206, '2216', 9999),
(207, '2217', 9999),
(208, '2218', 9999),
(209, '2219', 9999),
(210, '2220', 9999),
(211, '2221', 9999),
(212, '2222', 9999),
(213, '2223', 9999),
(214, '2224', 9999),
(215, '2225', 9999),
(216, '2226', 9999),
(217, '2227', 9999),
(218, '2228', 9999),
(219, '2229', 9999),
(220, '2230', 9999),
(221, '2231', 9999),
(222, '2232', 9999),
(223, '2233', 9999),
(224, '2234', 9999),
(225, '2250', 9999),
(226, '2251', 9999),
(227, '2256', 9999),
(228, '2257', 9999),
(229, '2258', 9999),
(230, '2259', 9999);



CREATE TABLE IF NOT EXISTS `locations_2` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `title` varchar(255) DEFAULT '',
  `position` int(11) NOT NULL DEFAULT '9999',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=6 ;



INSERT INTO `locations_2` (`id`, `title`, `position`) VALUES
(1, '2209', 9999),
(2, '2211', 9999),
(3, '2221', 9999),
(4, '2234', 9999),
(5, '2258', 9999);

Open in new window

0
sabecs
Asked:
sabecs
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
I understand you need this
insert into locations_2` (`id`, `title`, `position`)
select l.id, l.title, l.position
from locations l
where exists(select null from staff_locations sl
                where sl.location_id = l.id 
  )

Open in new window

1
 
sabecsAuthor Commented:
Thanks guy, that is perfect
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now