Sql-server – SQL simple update returning an error

oraclesql serverupdate

Oracle SQL and Microsoft SQL Server:-

I have a single table in which I wish to set two 'USERFIELD' column values, for all rows having their other column values set to a given literal.

SET ESCAPE ON
UPDATE DOCUMENT
SET USERFIELD4 = 'Europe \& International', USERFIELD7 = 'E\&I Engineering'
WHERE DOCNO in 
   (SELECT d.DOCNO FROM DOCUMENT d 
    WHERE d.USERFIELD4 = 'Combat Air' 
      and d.USERFIELD3 = 'Engineering' 
      and d.WITHDRAWN=0)

DOCNO is the table unique key column.

So, what I want is that if for a given row, USERFIELD4 = 'Combat Air' and USERFIELD3 = 'Engineering' and WITHDRAWN=0, then for that same row, set USERFIELD4 to 'Europe & International' and set USERFIELD7 to 'E&I Engineering', and for this to apply to all rows in the table.

I am getting the following error:

SQL Error: Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

Best Answer

The issue was a poorly written QueryPlan in response to a defined Trigger as per @Aaron Bertrand presumed. I rewrote the QueryPlan to return more than one row which was what was causing the rather misleading error message.

Related Question