mysql complex update and replace

Question

Hey everyone,

I've got an interesting query that I wonder if anyone can help with?

Basically, I have three tables, Title, Attribute and Attribute_Value

Title contains a list of templates like 'Created on {Date} by {User} in {Category}' then in the Attribute table, I have rows that contain a definition of the parameters wrapped in curly brackets.

Then within the attribute_value table, I have the values.

The problem I have is that the Query doesn't seem to store the replace on each row until the query is complete, which means the row only receives the last update, incomplete.

update Title inner join Attribute_Value on Attribute_Value.GenericID = Title.CollectibleID and Attribute_Value.GenericType = 'collectible' inner join Attribute on Attribute.AttributeID = Attribute_Value.AttributeID set Title.Title = replace(Title.Title, concat('{', Attribute.Name, '}'), Attribute_Value.Value)

So basically, it will replace {Date} correctly, but then when it goes to replace {User}, {Date} is not the replaced value but still {Date} and same with {Category}. But when it's finished, the

Any idea's on how I can update the Title column with the values, whilst keeping the previously replaced values?

Cheers

Gav

Answer 1

If the list of attributes is static, I would probably go with subqueries and a nested SQL REPLACE(). Otherwise, write your own SQL function to iterate through all possible attribute names and make the substitutions.

Answer 2

You could write a stored procedure that uses a cursor to update the attributes in Title one at a time.