MYSQL variables - SET @var
- by Lizard
I am attempting to create a mysql snippet that will analyse a table and remove duplicate entries (duplicates are based on two fields not entire record)
I have the following code that works when I hard code the variables in the queries, but when I take them out and put them as variables I get mysql errors, below is the script
SET @tblname = 'mytable';
SET @fieldname = 'myfield';
SET @concat1 = 'checkfield1';
SET @concat2 = 'checkfield2';
ALTER TABLE @tblname ADD `tmpcheck` VARCHAR( 255 ) NOT NULL;
UPDATE @tblname SET `tmpcheck` = CONCAT(@concat1,'-',@concat2);
CREATE TEMPORARY TABLE `tmp_table` (
`tmpfield` VARCHAR( 100 ) NOT NULL
) ENGINE = MYISAM ;
INSERT INTO `tmp_table` (`tmpfield`) SELECT @fieldname FROM @tblname GROUP BY `tmpcheck` HAVING ( COUNT(`tmpcheck`) > 1 );
DELETE FROM @tblname WHERE @fieldname IN (SELECT `tmpfield` FROM `tmp_table`);
ALTER TABLE @tblname DROP `tmpcheck`;
I am getting the following error:
#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 '@tblname ADD `tmpcheck` VARCHAR( 255 ) NOT NULL' at line 1
Is this because I can't use a variable for a table name? What else could be wrong or how wopuld I get around this issue.
Thanks in adavnce
