MYSQL variables - SET @var

Posted by Lizard on Stack Overflow See other posts from Stack Overflow or by Lizard
Published on 2010-05-13T11:46:42Z Indexed on 2010/05/13 12:04 UTC
Read the original article Hit count: 139

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

© Stack Overflow or respective owner

Related posts about mysql

Related posts about variables