subquery returned more than one value

[Mary Phelps]

When I run this query:
update a set a.[UPC] = b.[IDSCE], a.[AltDescription] = b.[IDESC], a.
[Weight] = b.[IMNNWU],
from [ProductVariant] a inner join [IIMWEB] b on a.
[SKUSuffix]=b.[IPROD]

I receive an error:
The statement has been terminated.
Msg 512, Level 16, State 1, Procedure trig_NotificationProduct, Line
19
Subquery returned more than 1 value. This is not permitted when the
subquery follows =, !=, <, <= , >, >= or when the subquery is used as
an expression.

[Scott Morris]

"Mary Phelps" <icanh...@gmail.com> wrote in message
news:509dc9e2-11a0-4cdc...@v6g2000prd.googlegroups.com...

Pay special attention to the entire error message - in particular, the part
"Procedure trig_NotificationProduct". This name should give you a hint
about the source of the problem.

[Bob McClellan]

Mary,... I'm surprised you did not get a syntax error for the extra
comma....

When I run this query:
update a
set a.[UPC] = b.[IDSCE],
a.[AltDescription] = b.[IDESC],

a.[Weight] = b.[IMNNWU], --< extra comma

from [ProductVariant] a
inner join [IIMWEB] b on a.[SKUSuffix]=b.[IPROD]

>>I think this part: Subquery returned more than 1 value <<
is telling you that you have more than one match on a.[SKUSuffix]=b.[IPROD]

"Mary Phelps" <icanh...@gmail.com> wrote in message
news:509dc9e2-11a0-4cdc...@v6g2000prd.googlegroups.com...

[--CELKO--]

Google this newsgroup and read all the postings about why we do not
use the proprietary and dangerous UPDATE.. FROM.. syntax. You can use
the MERGE statement and be safe and be writing SQL instead of
dialect.

MERGE INTO A -- really awful name
USING IIMWEB AS B -- really bad alias
ON A.sku_suffix = B.iprod
WHEN MATCHED
THEN UPDATE
SET A.upc = B.idsce,
A.alt_something_description = B.idesc,
A.something_weight = B.imnnwu;

This will spot your cardinality errors. You will still get errors from
stinky data. And you should. FIX THE STINKY DATA!

I am sorry that you do not have consistent data element names in your
enterprise, but that is no reason to use aliases that make no sense or
table names like "A" in your code. Why would B tell someone
maintaining the code that we want IIMWEB?

It looks like some moron put the data types in the IIMWEB column
names. Storing a UPC as an integer is just plain wrong.