[SOLVED] MySQL Update Query: I Can't find the cause of this Syntax Error 1064

MySQL Update Query: I Can't find the cause of this Syntax Error 1064

I am trying to run an update query that modifies the entry in a field via two statements. I keep getting a syntax error 1064 in Workbench. If I run each statement separately in two different queries, they both work. What I want is for both statements to be in one single query but I keep getting the 1064 error. The code is below.

UPDATE `tbloldfurniture`

SET `tbloldfurniture`.`NotesOnOldness` = UPPER(`NotesOnOldness`),
SET `tbloldfurniture`.`NotesOnOldness` = TRIM(`NotesOnOldness`)

WHERE rwID = 3;

What am I doing wrong? The full error message is below:

Error Code: 1064. You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SET `tbloldfurniture`.`NotesOnOldness` = LOWER(`NotesOnOldness`) WHERE rwID = 3' at line 4

Thanks.

Solution

It should be:

UPDATE `tbloldfurniture`
SET `tbloldfurniture`.`NotesOnOldness` = TRIM(UPPER(`NotesOnOldness`))
WHERE rwID = 3;

If you want to set multiple columns, you separate the assignments only with ,, you don't repeat the word SET before each. But in this case you're only setting one column, so you don't need two assignments. Just nest the function calls in a single expression.