sabecs
asked on
MySQL - need to modify one table with contents from other table?
Hi,
Can anyone provide an SQL query to get all distinct `location_id` from staff_locations table and add to locations_2 table as `title`.
Perhaps emptying the `locations_2` table might be the best option first?
Thanks in advance.
Can anyone provide an SQL query to get all distinct `location_id` from staff_locations table and add to locations_2 table as `title`.
Perhaps emptying the `locations_2` table might be the best option first?
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=21 ;
INSERT INTO `staff_locations` (`id`, `staff_id`, `location_id`) VALUES
(9, 1, 200),
(10, 1, 202),
(1, 1, 757),
(2, 1, 758),
(3, 1, 759),
(4, 1, 770),
(5, 1, 771),
(6, 1, 773),
(7, 1, 774),
(8, 1, 776),
(9, 1, 1641),
(10, 1, 1642),
(11, 2, 1636),
(12, 2, 1637),
(13, 2, 1638),
(14, 2, 1639),
(15, 2, 1641),
(16, 2, 1642),
(17, 2, 1645),
(18, 2, 1646),
(19, 2, 1647),
(20, 2, 1648);
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=203 ;
INSERT INTO `locations_2` (`id`, `title`, `position`) VALUES
(1, '200', 9999),
(2, '202', 9999);
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks Ryan, your query works well expect I discovered I need the title from another table called locations, I will accept your solution and re post another question.
ASKER
Thanks
it's depends... for the comment I posted above it will only inserting the new entries into table: locations_2