Updating in MySQL not working

Portal Home > Knowledgebase > Articles Database > Updating in MySQL not working

Posted by FinalFrontier, 11-23-2011, 01:12 AM
Hello, I am trying to update two rows in a MySQL query, but not having any luck getting it working and hoping someone could give me a hand to point out where I have stuffed up? The query is: UPDATE orders SET events = CONCAT(events,test2\n) AND status = 'complete' WHERE id = 1;
Posted by Squidix - SamBarrow, 11-23-2011, 01:13 AM
The AND should be a comma. And I don't know what test2\n is but you probably want to put that in quotes?
Posted by FinalFrontier, 11-23-2011, 01:18 AM
UPDATE orders SET events = CONCAT(events,test2\n), status = 'complete' WHERE id = 1; Solved.
Posted by FinalFrontier, 11-23-2011, 01:20 AM
Yes Sam, you were correct the comma was the problem. The \n is a line brake.
Posted by foobic, 11-23-2011, 01:29 AM
Have you considered making events a separate table joined to orders by the order id? Your update would then be something like: (and then you'd probably add other fields to record when the update happened, who did it etc.) Normalization isn't always the best policy but whenever you find yourself using concatenation it's certainly worth considering! Last edited by foobic; 11-23-2011 at 01:31 AM. Reason: Added order status update
Posted by Squidix - SamBarrow, 11-23-2011, 01:39 AM
From an architectural standpoint you would be better off doing what foobic suggested too. Nothing is more frustrating than trying to parse a database full of concatenated columns for one to many relationships.
Posted by FinalFrontier, 11-23-2011, 01:40 AM
I've considered a seperate tables, but it gets too messy for what I need. The events row is used for HTML only, it will have no rational database use. Thanks for the suggestion anyway. It is very much appreciated guys! Last edited by FinalFrontier; 11-23-2011 at 01:43 AM.

Add to Favourites Print this Article

Knowledgebase

Updating in MySQL not working

Our Services

Client Menu

Legal