0

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,

4
  • I think the error is coming from one of the updates in the first set. In your second update, the CASE expression is not needed, and you could just assign Internalcharno directly to EDIT. Commented Feb 8, 2018 at 4:48
  • I think error fro here : FROM (SELECT CharacteristicValue FROM AUSP WHERE (Inte, you have to apply TOP 1 OR update where condition where it return only one record Commented Feb 8, 2018 at 4:51
  • Can you @TimBiegeleisen expain me about the CASE expression is not needed ? Commented Feb 8, 2018 at 4:52
  • you need to keep where condition values in a table variable then try it.. Commented Feb 8, 2018 at 4:53

1 Answer 1

1

As You changed your question,I think problem is not in your last update query but somewhere here:

SELECT @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

Make sure your every subquery is giving you a single value.

Sign up to request clarification or add additional context in comments.

1 Comment

Pardon me, the values is not equal in every Internalcharno condition

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.