Error executing update with subquery

Question

Error executing update with subquery

Navigation

#1 by (0 votes)

0

I have the following situation, I need to perform an update on several fields based on the return of a subquery that returns a main code. This subquery is done with join in two tables to get the code. While doing this, I get the following error:

Message 512, Level 16, State 1, Line 24 The subquery returned more than 1 value. This is not allowed when the subquery follows a =,! =, & Lt ;, < =, & gt ;, > = or when it is used as an expression.

This is the query:

BEGIN TRANSACTION;
UPDATE Produto SET TpProduto = 'C' WHERE IdProduto = (
SELECT DISTINCT(p.IdProduto)
FROM dbo.Produto p 
JOIN dbo.CodigoProduto  cp ON cp.IdProduto = p.IdProduto
WHERE cp.CdChamada IN(
'3VT9200-4ED30                 ',
'3VT9225-6AC00                 ',
'5SX11207                      ',
'6ES73921AJ000AA0              ',
'8910427                       ',
'9676812                       ',
'CON000280                     '

 )

)

sql sql-server

asked by anonymous 11.12.2018 / 19:21

1 answer

PDO Duplicate Data Update Problem Automatic payment notification PagSeguro

score 0 · Accepted Answer

this select return more than one value

SELECT DISTINCT(p.IdProduto)

how to compare

WHERE IdProduto = (vários valores)

This piece here needs to return only one value

SELECT DISTINCT(p.IdProduto)
FROM dbo.Produto p 
JOIN dbo.CodigoProduto  cp ON cp.IdProduto = p.IdProduto
WHERE cp.CdChamada IN(
'3VT9200-4ED30                 ',
'3VT9225-6AC00                 ',
'5SX11207                      ',
'6ES73921AJ000AA0              ',
'8910427                       ',
'9676812                       ',
'CON000280                     '

 )

Check to see if this is not the case.