SQL Server Error Msg 115 – The FOR UPDATE clause is invalid for statements containing set operators.

In this blog post, let’s learn about the error message “115 – The FOR UPDATE clause is invalid for statements containing set operators.” in Microsoft SQL Server, the reason why it appears and the solution to fix it.

SQL Server Error Message

115 – The FOR UPDATE clause is invalid for statements containing set operators.

Reason for the Error

The SQL Server Error Msg 115 – The FOR UPDATE clause is invalid for statements containing set operators, occurs when the “FOR UPDATE” clause is used in a SQL statement that contains set operators such as UNION, INTERSECT, or EXCEPT. The “FOR UPDATE” clause is used to lock the rows that are being updated or deleted, but it cannot be used with set operators that return rows from multiple tables.

Here’s an example that causes the error:

SELECT id FROM table1
UNION
SELECT id FROM table2
FOR UPDATE;

In this example, the “FOR UPDATE” clause is used after a UNION operator, which is not allowed. This would result in the SQL Server error message 115.

You may also receive an error “Line %d: %ls clause allowed only for %ls.” in the latest version of SQL Server.

Here are some possible reasons for this error:

1. Incorrect syntax: The SQL statement may have incorrect syntax or be missing required elements, which causes the “FOR UPDATE” clause to be used incorrectly.
2. Incorrect usage of set operators: The “FOR UPDATE” clause may be used incorrectly in a SQL statement that contains set operators.

Solution

To fix this error, you can try the following solutions:

Remove the “FOR UPDATE” clause from the SQL statement.

SELECT id FROM table1
UNION
SELECT id FROM table2;

Use a different locking method or query hint to lock the rows that are being updated or deleted.

SELECT id FROM table1 WITH (UPDLOCK)
UNION
SELECT id FROM table2 WITH (UPDLOCK);

In this example, the “UPDLOCK” query hint is used instead of the “FOR UPDATE” clause to lock the rows that are being updated or deleted.