Wednesday, 2 October 2013

How to use if else and exist to drop table in MySQL when create view

How to use if else and exist to drop table in MySQL when create view

using both methods got error in workbench
method 1 got error
CREATE VIEW `db2`.`view2` AS
DROP PROCEDURE IF EXISTS `temp_table` $$
method 2 got error Error Code: 1351. View's SELECT contains a variable or
parameter method 2
CREATE VIEW `db2`.`view2` AS
SELECT IF(EXISTS(SELECT * FROM information_schema.tables WHERE
table_schema = 'db2' AND table_name = 'temp_table' LIMIT 1),1,0) into
@testresult;
IF (@testresult > 0) THEN
drop table temp_table;
END IF;
CREATE TEMPORARY TABLE temp_table (count int);
insert into temp_table select 1;

No comments:

Post a Comment