Simple Update - Error?

Transact-SQL
1

So I'm trying to do a simple update to a table, here's the code:

update Products
set REGION = '0382'
where CITY = '9382'

Simple as that. Some already have the REGION set to 0382, some are blank, just want to fill in the blank ones (or correct any potentially incorrect records).

I'm getting the following error:

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

I'm I going about this the wrong way?

Thanks!

2

Is Products a base table, or is it a view?

SELECT type_desc,* FROM sys.objects
WHERE name = 'Products'

Also, if Products is a table, does it have any triggers on it?

SELECT * FROM sys.triggers
WHERE OBJECT_NAME(parent_id) = 'Products'
3

It was a trigger! Thanks, was able to disable it, make my changes and reenable it.

4

Is it perhaps, wrongly, designed to only expect a single row to be updated at once? If so I suggest you change the trigger (and any others!) so that they can handle multiple rows.

I've had errors raised in triggers causing me to scratch my head for ages trying to work out what was wrong with my main statement :slight_smile: The error message will include the location of the error, in this case the name of the trigger, which is worth watching out for. Unfortunately in my case we use the name XX_SP_YYY_ZZZ for the main SProc on the table and XX_TR_YYY_ZZZ for the trigger and I often don't spot the subtle difference in the error message :frowning: