MySQL Update Error - Cannot find what is wrong

Question

I am working on a CFML project with a MySQL backend. I have a function that contains an SQL update statement. The cfquery looks like this:

<cfquery name="del" datasource="#APPLICATION.db.source#" username="#APPLICATION.db.user#" password="#APPLICATION.db.pass#">
    UPDATE changemgmt.rfc
    SET deleted_timestamp = <cfqueryparam cfsqltype="CF_SQL_TIMESTAMP" value="#Now()#">,
    SET deleted_by = <cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#SESSION.user.name#">
    WHERE rfc_id = <cfqueryparam cfsqltype="CF_SQL_INTEGER" value="#arguments.rfc_id#">;
</cfquery>

This is the error that is being returned:

Lucee 5.3.7.48 Error (database)
Message 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 deleted_by = 'first.last'
WHERE rfc_id = 3' at line 3
SQL UPDATE changemgmt.rfc
SET deleted_timestamp = modified_timestamp = {ts '2022-09-12 10:14:13'},
SET deleted_by = 'first.last'
WHERE rfc_id = 3;

Clearly, the error is telling me that I have an SQL syntax problem. If I remove the SET deleted_timestamp line, the function/query executes just as it should. I have spent a great deal of time on this and for the life of me cannot figure out why the error is being thrown. Can anyone tell me why what I have is not working?

"You have an error in your SQL syntax" is always a message from the SQL server. Lucee is not involved when it comes to parsing and executing SQL statements. Lucee is simply putting the error feedback from the return channel into the exception and throws it. — Alex
– Alex, Commented Sep 14, 2022 at 22:14

Michael Buckman · Accepted Answer · 2022-09-12 16:40:23Z

0

I have discovered my error. I need to remove the second SET keyword from the SQL statement. Also, Dreamweaver was being quirky and not reflecting my edits. I closed Dreamweaver and reopened it and it was as if I hadn't made any edits when in fact I had.

edited Sep 12, 2022 at 16:40

answered Sep 12, 2022 at 16:33

Michael Buckman

15011 bronze badges

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

1 Comment

bosowski Over a year ago

If you want to update multiple values with a single set then the correct syntax is ‘set table update column1=value1, column2=value2 where…’

Collectives™ on Stack Overflow

MySQL Update Error - Cannot find what is wrong

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related