Working only with MySQL (I have essentially no PHP knowledge), I need to have a table that's essentially a subset from a much larger table. The source table changes from time to time, losing some entries, gaining other new ones, and values changing for existing ones. I can describe what I want to happen, but can't seem to figure out a syntax of commands to make it work. I also know I can have two separate queries and just run whichever one I need, and I have that worked out, but I'd like to combine them if possible. Here's what I want:
IF the subset_table DOES NOT EXIST, create it as [select query], ELSE truncate the subset_table and insert [select query]
Like I said, I know there are other ways to do this - I could drop if exists/create, or I could just have two different sql files to run. I just want to know if I can do this as specified above.
Thoughts?
TRUNCATE TABLE locks the whole table to remove data from a table; thus, this command also uses less transaction space than DELETE . Unlike DELETE , TRUNCATE does not return the number of rows deleted from the table. It also resets the table auto-increment value to the starting value (usually 1).
DROP TABLE MySQL Command Syntax. To remove a table in MySQL, use the DROP TABLE statement. The basic syntax of the command is as follows: DROP [TEMPORARY] TABLE [IF EXISTS] table_name [, table_name] [RESTRICT | CASCADE];
No. In general you have to DROP the table first before CREATE succeeds. There are lots of exceptions to this when it comes to actual implementations, such as if the table is temporary or permanent, access rights where multiple users can have the same table name as long as they all use different user names.
You can do this:
create table if not exists <tablename> . . .;
truncate table <tablename>;
insert into <tablename>(cols)
select blah blahblah . . .;
You don't need any if
statements at all.
This can also be done through an SP (stored procedure)... makes it more readable and safe
DELIMITER $$
DROP PROCEDURE IF EXISTS `create_table_sp`$$
CREATE PROCEDURE `create_table_sp`()
BEGIN
IF NOT EXISTS (SELECT 1 FROM information_schema.TABLES WHERE table_name = '<table_name>'
AND table_schema = DATABASE() AND table_type = 'BASE TABLE') THEN
CREATE TABLE <subset_table_name>
AS SELECT * FROM <main_table_name>;
ELSE
TRUNCATE TABLE <subset_table_name>;
INSERT INTO <subset_table_name>
AS SELECT * FROM <main_table_name>;
END IF;
END$$
DELIMITER ;
CALL `create_table_sp`;
DROP PROCEDURE IF EXISTS `create_table_sp`;
There is also another way,
Hope this helped....
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With