set variable to column name mysql

Question

I am trying to loop through a table adl_weight that has a column with the exact names of a column from another table adl_activities. I am trying to grab the column name from the table and use that to grab the value of the column on the second table.

Is it possible to do this with mysql? I have tried to use prepared statements but so far these aren't working.

set i = 1;
select count(*) from adl_weight into n;

WHILE(i<n) DO
    set tmp_condition =(select user_condition from adl_weight where row_number = i);
    set tmp_score = (select tmp_condition from adl_activities where userid = id);

    if(tmp_score > 0) then
        #do items here
    end if;
END WHILE;

Are the column names known? If so, I would think that you could use a JOIN, no? — Patrick Q
– Patrick Q, Commented Jul 23, 2014 at 21:02
Is there a column that shares a common value between the 2 tables? — Jestep
– Jestep, Commented Jul 23, 2014 at 21:06
The column names are known but there is no columns that share the same value. A join will not work as I still need to pull data from that column using a variable so I would still be at step 1. — user1267762
– user1267762, Commented Jul 23, 2014 at 21:22

Thomas · Accepted Answer · 2014-07-24 00:10:39Z

2

If I understand you correctly, you need to grab a column whose name is defined in a variable.

The problem is that mysql doesn't let you insert variables as anything other than values, but you can create a new string which contains a command with the variable's value as the column name and execute that. Here's an example:

SET @sql = CONCAT("SELECT `", @columnName, "` FROM tableA");
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

Edit: If you want to select into a variable instead, you can use this:

SET @sql = CONCAT("SELECT ", @columnName, "` FROM tableA INTO @result");

edited Jul 24, 2014 at 0:10

answered Jul 23, 2014 at 21:43

Thomas

9113 gold badges9 silver badges21 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

skarist Over a year ago

just want to point out typos. You misspelled the name of the CONCAT function, and instead of an equal sign you have a '-'. (I can't edit your post when the edits are "so trivial", must change at least 6 characaters) +1 from me. Good solution to his problem.

user1267762 Over a year ago

I am able to do that I was just wondering how can I assign the result back into a variable I can use. Perhaps say I run it and I get back the number 2 how can I integrate that into my process.

Thomas Over a year ago

Oh, that's quite simple! SET @sql = CONCAT("SELECT ", @columnName, "` FROM tableA INTO @result");`

Collectives™ on Stack Overflow

set variable to column name mysql

1 Answer 1

3 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Related