MySQL query, how to use string in multiple ways?

Question

I'm trying to create a MySql query which will construct a string using the CONCAT function within 2 loops.

I'm wondering if a string can be used both as a variable and a reference to a field in a table?

My code so far is below, which is returning some syntax error near the UPDATE line:

    ...

WHILE x  <= 5 DO
      WHILE y  <= 3 DO
                SELECT @str_data = CONCAT(str,'data_',x); 
        SELECT @str_data = CONCAT(str,'_',y); 

        SELECT @variable:= @str_data FROM sometable WHERE id = '@counter + y';

        UPDATE anothertable SET @str_data = @variable WHERE id = '@counter + y';

            SET  y = y + 1;
    END WHILE;
    SET  x = x + 1;
    SET @counter = counter + 3;
END WHILE;

I've checked all variables are set.

On a side note I may not be using the concat correctly, since I saw it being used differently in multiple examples. I intend for the format to end up as: 'data_x_y'. (By adding the '_y' to the end of the current string. := ?)

Johan · Accepted Answer · 2011-06-04 12:50:33Z

1

Lets start at the beginning: Change this code:

SELECT @str_data = CONCAT(str,'data_',x); 
SELECT @str_data = CONCAT(str,'_',y);

To :

SET @str_data = CONCAT(str, 'data_',x,'_',y);

I'm wondering why you are mixing DECLARE variables (str) with @vars *(@str_data)*, it looks very confused.

So this code: SELECT @variable:= @str_data FROM sometable WHERE id = @counter + y;

Does not do what you think it does. It just selects @str_data 0 or more times, whilst setting @variable.
You need to remove the ' quotes from that if you want MySQL to add @counter + y because with a quote it will just evaluate to 0 and not match.

This code is incorrect:

UPDATE anothertable SET @str_data = @variable WHERE id = '@counter + y';

Change it to:

UPDATE anothertable SET fieldx = @variable WHERE id = @counter + y;

Note that SQL is not php, you cannot use @vars as placeholders for dynamic column names.
It really makes no sense.
I guess you are trying to come up with a string holding a dynamic column name and doing a update on that column.
Forget about using this approach, it will never work in SQL, also this is not the way SQL is supposed to work.

What to do instead

Change your table into this format

Table proper
  x integer
  y integer
  value varchar
  otherfields .....

Now you can just update with

 UPDATE proper SET value = @var WHERE x = 1 and y = 2;

Much cleaner and much faster.

edited Jun 4, 2011 at 12:50

answered Jun 4, 2011 at 11:24

Johan

77.4k28 gold badges204 silver badges346 bronze badges

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

2 Comments

user723789 Over a year ago

Thanks for the response, but this was the issue. I need the string to be a reference to a field in a table. The string conetent itself (example: "data_1_2"), will become the name of a field which is used.

Johan Over a year ago

@user, use php if you must, but it will be slow for large tables, see my update to the answer instead.

Collectives™ on Stack Overflow

MySQL query, how to use string in multiple ways?

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related