Search code examples
sqlsql-serversql-updatesubquery

Simple update query return Error [512] [S0001]: Subquery returned more than 1 value

I'm trying to update a column on a table with a simple query like this:

update tes_off_ven
set delivery_date=null
where company_id ='A01' and  registration_year = 2022 and registration_number = 183

but SqlServer return the following error:

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

The error is returned only if I try to update a specific record, but works fine with another record, for example with the following where clause:

where company_id ='A01' and  registration_year = 2022 and registration_number = 182

But there is another strange behavior...the supersimple/supermassive update query without any where clause

update tes_off_ven
set delivery_date=null

works fine!

The tes_off_ven table the has the three fields company_id, registration_year and registration_number in primary key and many tables are linked with tes_off_ven by theese fields.

If I truncate all tes_off_ven linked tables the update query works fine.

Solution

  • As suggested by @Dan Guzman and @Delta32000 the problem was due by a trigger.