Client Area

How to Fix MySQL Error 1204 – SQLSTATE: HY000 (ER_SET_CONSTANTS_ONLY) You may only use constant expressions with SET

How to Fix MySQL Error 1204 – SQLSTATE: HY000 (ER_SET_CONSTANTS_ONLY)

MySQL is a popular open-source relational database management system used by many websites and applications. However, like any software, it can encounter errors that can disrupt its normal operation. One such error is MySQL Error 1204 – SQLSTATE: HY000 (ER_SET_CONSTANTS_ONLY). In this article, we will explore what this error means and how to fix it.

Understanding MySQL Error 1204

MySQL Error 1204 occurs when you try to execute a query that includes a non-constant expression in the SET clause. The error message, SQLSTATE: HY000 (ER_SET_CONSTANTS_ONLY), indicates that only constant expressions are allowed in the SET clause.

Here’s an example of a query that can trigger this error:

UPDATE users SET last_login = NOW() WHERE id = 1;

In this query, the NOW() function is used to set the value of the last_login column to the current date and time. However, since NOW() is not a constant expression, it causes the error.

Fixing MySQL Error 1204

To fix MySQL Error 1204, you need to ensure that only constant expressions are used in the SET clause. Here are a few approaches you can take:

1. Use a Variable

Instead of using a non-constant expression directly in the SET clause, you can assign it to a variable first. Then, use the variable in the SET clause. Here’s an example:

SET @current_time = NOW();
UPDATE users SET last_login = @current_time WHERE id = 1;

By assigning the result of the NOW() function to the @current_time variable, you can use the variable in the SET clause without triggering the error.

2. Use a Subquery

Another way to avoid the error is by using a subquery to retrieve the non-constant value. Here’s an example:

UPDATE users SET last_login = (SELECT NOW()) WHERE id = 1;

In this query, the subquery (SELECT NOW()) returns the current date and time as a constant value, which can be safely used in the SET clause.

3. Use a Constant Value

If the non-constant expression is not necessary and you only need a constant value in the SET clause, you can simply use a constant value directly. Here’s an example:

UPDATE users SET last_login = '2022-01-01' WHERE id = 1;

In this query, the constant value ‘2022-01-01’ is used to set the value of the last_login column.

Summary

MySQL Error 1204 – SQLSTATE: HY000 (ER_SET_CONSTANTS_ONLY) occurs when a non-constant expression is used in the SET clause of a query. To fix this error, you can use variables, subqueries, or constant values instead. If you encounter this error while working with MySQL, consider implementing one of the solutions mentioned above.

For reliable and high-performance VPS hosting solutions, consider Server.HK. Our Hong Kong VPS hosting services are designed to meet the needs of businesses and individuals looking for secure and scalable hosting solutions.