sabecs
asked on
MySQL - how to create and select temp tables?
Hi,
I am just wondering how I would go about creating and selecting a temp MySQL table?
Can it be the same name as an existing table.
I want to reduce size of existing "wp_ab_staff_locations" table to a specific location.
Thanks in advance.
I am just wondering how I would go about creating and selecting a temp MySQL table?
Can it be the same name as an existing table.
I want to reduce size of existing "wp_ab_staff_locations" table to a specific location.
Thanks in advance.
CREATE TEMPORARY TABLE IF NOT EXISTS wp_ab_staff_locations AS (SELECT * FROM wp_ab_staff_locations WHERE location_id= '754' )
SELECT * FROM wp_ab_staff_locations;
CREATE TABLE IF NOT EXISTS `wp_ab_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=96 ;
--
-- Dumping data for table `wp_ab_staff_locations`
--
INSERT INTO `wp_ab_staff_locations` (`id`, `staff_id`, `location_id`) VALUES
(9, 1, 200),
(10, 1, 202),
(1, 1, 757),
(4, 1, 770),
(5, 1, 771),
(6, 1, 773),
(7, 1, 774),
(8, 1, 776),
(37, 3, 754),
(38, 3, 755),
(39, 3, 756),
(40, 3, 757),
(41, 3, 758),
(42, 3, 759),
(43, 3, 760),
(87, 5, 754),
(88, 5, 755),
(89, 5, 756),
(94, 5, 761),
(95, 5, 762);
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 for your comments, I haven't used temp tables before but from what I understand I can create for a new table for each visitor to my website specific to their location. So when they search for staff they will only see staff in their location?
ASKER
I have tried creating a temp table using phpMyAdmin form cPanel, seems to create table but I get an error when I try and view table?
CREATE TEMPORARY TABLE IF NOT EXISTS wp_ab_staff_locations AS (SELECT * FROM wp_ab_staff_locations WHERE location_id= '754' );
SELECT * FROM wp_ab_staff_locations;
#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT * FROM wp_ab_staff_locations' at line 2
CREATE TEMPORARY TABLE IF NOT EXISTS wp_ab_staff_locations AS (SELECT * FROM wp_ab_staff_locations WHERE location_id= '754' );
SELECT * FROM wp_ab_staff_locations;
#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT * FROM wp_ab_staff_locations' at line 2
ASKER
sorry, I had a typo, it works well.
Great ! Could you please mark one solution as accepted solution and close the question.
Thank you !
Thank you !
>>but from what I understand I can create for a new table for each visitor to my website specific to their location. So when they search for staff they will only see staff in their location
you NO need specifically to create temporary table for your visitor since your original table can serve the same purpose. And to store operational data (like visitor activities to your site) to temporary tables could be a risk if your MySQL Server facing error and being forced to restart the service. Your data could be gone.
just write to physical tables would be a better approach. use temporary tables for other purposes instead.
you NO need specifically to create temporary table for your visitor since your original table can serve the same purpose. And to store operational data (like visitor activities to your site) to temporary tables could be a risk if your MySQL Server facing error and being forced to restart the service. Your data could be gone.
just write to physical tables would be a better approach. use temporary tables for other purposes instead.
ASKER
Thanks
why you intend to do that?
>>I want to reduce size of existing "wp_ab_staff_locations" table to a specific location.
So why you move your data into a temporarily table? Why you don't store your existing data into an archive table (another table) instead?