Solved

MySQL - update table from 2 other tables.

Posted on 2016-08-22
2
53 Views
Last Modified: 2016-08-22
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
Comment
Question by:sabecs
2 Comments
 
LVL 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
ID: 41766375
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
 

Author Comment

by:sabecs
ID: 41766386
Thanks guy, that is perfect
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
xampp tool 12 24
mysqli 3 18
WordPress syntax 2 25
how to use a function in heredoc 5 21
This article discusses how to create an extensible mechanism for linked drop downs.
Introduction This article is intended for those who are new to PHP error handling (https://www.experts-exchange.com/articles/11769/And-by-the-way-I-am-New-to-PHP.html).  It addresses one of the most common problems that plague beginning PHP develop…
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…

912 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now