CODEWITHSUNDEEP
mysqlsql
user1464296
user1464296

Reputation:

How to use variable as the table identifier in MYSQL

If i have this:

CREATE TABLE ftable (
id     INT,
fvalue VARCHAR(14)
);

INSERT INTO ftable VALUES (1,'tableB'),(2,'tableA');

CREATE TABLE tableA (
value VARCHAR(14)
);

SELECT @tmp:=fvalue FROM ftable WHERE id=2;

How do I make it so I can do this:

INSERT INTO @tmp VALUES ('buhambug');

Becuase as far I know that throws a mysql error.Can someone show me a sqlfiddle of the solution? Or maybe I'm thinking about this the wrong way?

Upvotes: 3

Views: 309

Answers (2)

GarethD
GarethD

Reputation: 69789

You need to use dynamic SQL to use a variable as an object name:

SET @tmp = (SELECT fvalue FROM ftable WHERE id=2);

SET @SQL = CONCAT('INSERT INTO ',@tmp,' VALUES (''buhambug'')');
PREPARE stmt FROM @SQL;
EXECUTE stmt;

SQL FIDDLE

Upvotes: 4

ravnur
ravnur

Reputation: 2852

You can't do in static sql. You can do it in stored procedure:

delimiter $$

drop procedure if exists test_call$$

create procedure test_call(table_in varchar(100))
begin
  set @q = concat("select * from ", table_in);

  PREPARE stmt FROM @q;
  EXECUTE stmt;
  DEALLOCATE PREPARE stmt;

end$$

delimiter ;

call test_call('TeableA');

drop procedure if exists test_call;

In general dynamic read from dynamic tables is not a good decision

Upvotes: 0

Related Questions