CODEWITHSUNDEEP
mysqldynamic-sql
wUmpage
wUmpage

Reputation: 165

Dynamic MySQL Update Statement

I'm trying to write a prodecure that updates a value in a given column-name where the users id equals given user ID.

_strong_1 is a variable that contains the column name, i.e: 'category_1', for example.

SELECT COLUMN_NAME FROM information_schema.`COLUMNS` C 
WHERE table_name = 'subscribers_preferences' AND COLUMN_NAME LIKE _strong_1 INTO @columns;

SET @table = 'subscribers_preferences';
SET @s = CONCAT('UPDATE ',@table,' SET ', @columns = 1);

PREPARE stmt FROM @s;
EXECUTE stmt;

There's an error within the 'SET @s =' statement. I can get it to work with a simple SELECT statement, but UPDATE is being tricky.

Thanks in advance.

Upvotes: 0

Views: 5706

Answers (1)

Barmar
Barmar

Reputation: 780879

You need to put = 1 in quotes.

SET @s = CONCAT('UPDATE ',@table,' SET ', @columns, ' = 1');

Otherwise, you're comparing @columns with 1, and concatenating either 1 or 0 (probably always 0, since I doubt you have a column named 1) to the SQL, which is creating invalid SQL.

Note that the above code will only update one column. If @columns is supposed to hold 3 columns, you need to use GROUP_CONCAT in your query that sets it.

SELECT GROUP_CONCAT(CONCAT(column_name, ' = 1')) AS @columns
FROM information_schema.columns
WHERE table_name = 'subscribers_preferences' and column_name LIKE _strong_1;
SET @table = 'subscribers_preferences';
SET @s = CONCAT('UPDATE ',@table,' SET ', @columns);

I suspect you also need to add a WHERE clause to this SQL so it just updates the row for the given ID. As currently written, it will update all rows.

The fact that you need to write the query like this suggests improper normallization of your data. Instead of having each preference option in a different column, they should be different rows of the table, with the key being something like (user_id, setting_name).

Upvotes: 1

Related Questions