I have code like this to update multiple rows
--ZZSEQUENCENBR
UPDATE AUSP
SET CharacteristicValue = SUBSTRING(@RolNum, 5, 1)
WHERE Object = @Batch AND Internalcharno = 'ZZSEQUENCENBR'
--ZZPOSITIONMS
UPDATE AUSP
SET CharacteristicValue = SUBSTRING(@RolNum, 6, 1)
WHERE Object = @Batch AND Internalcharno = 'ZZPOSITIONMS'
--ZZDERIVATIVEMS
UPDATE AUSP SET CharacteristicValue = SUBSTRING(@RolNum,7,1) WHERE Object = @Batch AND Internalcharno = 'ZZDERIVATIVEMS'
--ZZPOSITIONSS
UPDATE AUSP SET CharacteristicValue = SUBSTRING(@RolNum,8,1) WHERE Object = @Batch AND Internalcharno = 'ZZPOSITIONSS'
--ZZDERIVATIVESS
UPDATE AUSP SET CharacteristicValue = SUBSTRING(@RolNum,9,1) WHERE Object = @Batch AND Internalcharno = 'ZZDERIVATIVESS'
--ZZNOMORROLL
UPDATE AUSP SET CharacteristicValue = @RolNum WHERE Object = @Batch AND Internalcharno = 'ZZNOMORROLL'
--UPDATE Speed
UPDATE AUSP SET Valuefrom = @SPEED WHERE Object = @Batch AND InternalCharNo = 'ZZSPEED'
--Update Panjang, Lebar, berat & Xtra Length
UPDATE AUSP SET Valuefrom = @LebarActual WHERE Object = @Batch AND InternalCharNo = 'ZZWIDTH'
UPDATE AUSP SET Valuefrom = @PanjangActual WHERE Object = @Batch AND InternalCharNo = 'ZZLENGTH'
SELECT @Thick = ZZTYPEFILM.[ThicknessFilm(Micron)], @Density = ZZTYPEFILM.[Density(g/Cm3)] FROM (SELECT CharacteristicValue FROM AUSP WHERE (InternalCharNo = N'ZZCODE') AND (Object = @Batch)) AS View1 INNER JOIN ZZTYPEFILM ON View1.CharacteristicValue = ZZTYPEFILM.KodeFilm
SET @QtyRol = ROUND((@LebarActual * @PanjangActual * convert(float,@Thick) * convert(float,@density))/1000000,1)
UPDATE AUSP SET Valuefrom = @QtyRol WHERE Object = @Batch AND InternalCharNo = 'ZZCONVERSIONROLLKG'
UPDATE AUSP SET Valuefrom = @XtraPanjang WHERE Object = @Batch AND InternalCharNo = 'ZZEXLENGTH'
I was try code like this to update Internalcharno, not yet in Valuefrom :
UPDATE AUSP
SET CharacteristicValue = (case when Internalcharno = 'ZZPRODLINE' then 'EDIT'
when Internalcharno = 'ZZMONTHYEAR' then 'EDIT1'
when Internalcharno = 'ZZSEQUENCENBR' then 'EDIT2'
when Internalcharno = 'ZZPOSITIONMS' then 'EDIT3'
when Internalcharno = 'ZZDERIVATIVEMS' then 'EDIT4'
when Internalcharno = 'ZZPOSITIONSS' then 'EDIT5'
when Internalcharno = 'ZZDERIVATIVESS' then 'EDIT6'
when Internalcharno = 'ZZNOMORROLL' then 'EDIT7'
end)
WHERE Internalcharno in ('ZZPRODLINE', 'ZZMONTHYEAR', 'ZZSEQUENCENBR', 'ZZPOSITIONMS',
'ZZDERIVATIVEMS', 'ZZPOSITIONSS', 'ZZDERIVATIVESS', 'ZZNOMORROLL' ) AND
[Object] = '414095';
But this code throws an error :
Msg 512, Level 16, State 1, Procedure HistoryAUSPBeforeUpdate, Line 27
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
The statement has been terminated.
Can anyone help? Thanks,
CASEexpression is not needed, and you could just assignInternalcharnodirectly toEDIT.FROM (SELECT CharacteristicValue FROM AUSP WHERE (Inte, you have to apply TOP 1 OR update where condition where it return only one recordthe CASE expression is not needed?