Error message while duplicating purchasing lists from old database to a new database, msg 512, level 16

Question

Hi i keep getting this error message when i try to execute this sql script: Msg 512, Level 16, State 1, Line 12 Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

The thing we are trying to do is to duplicate some purchasing list stored at some customers from an old database to a new one, which somehow got lost during the transfer to the new database we made a couple of weeks ago, so we made this script, which is working for a couple of customers but for some it just gets the error code i wrote in bold.

I did find some old answers to this but i couldn't get it quite to work on my own, so i hope some of you could lend me a helping hand, as i cannot duplicate thousands of customer purchasing list manually, that would take some time.

Declare @OldWebuserNo INT
Declare @NewWebuserNo INT
Declare @OldCustNo INT
Declare @ZpiderOrderNo INT

/* Sett kundenummeret som skal endres for*/ 
SET @OldCustNo = 12001
while @OldCustNo <= 80000
BEGIN


/* Henter ut det nye webusernummeret for kunden */ 
use ZF0010 
select @NewWebuserNo = ( select ZpiderOrder.WebUserNo
from ZF0010.WebUser
join ZF0010.ZpiderOrder On ZF0010.ZpiderOrder.WebUserNo = ZF0010.WebUser.WebuserNo
where CustomerNo = @OldCustNo )

/* Lister ut ordrenummer på kunden med gammelt webusernummer til en midlertidig tabell, og oppdaterer WebuserNo på ordrene*/
use ZF0001
DECLARE @MyCursor CURSOR;
DECLARE @MyField INT;
BEGIN
    SET @MyCursor = CURSOR FOR
    select ZpiderOrderNo
    from ZF0001.ZpiderOrder
join ZF0001.WebUser On ZF0001.ZpiderOrder.WebUserNo= ZF0001.WebUser.WebuserNo
    where CustomerNo = @OldCustNo
    OPEN @MyCursor 
    FETCH NEXT FROM @MyCursor 
    INTO @MyField

    WHILE @@FETCH_STATUS = 0
    BEGIN
    update ZF0010.ZF0010.ZpiderOrder
    set WebUserNo = @NewWebuserNo
    where ZpiderOrderNo = @MyField

      FETCH NEXT FROM @MyCursor 
      INTO @MyField 
    END; 

    CLOSE @MyCursor ;
    DEALLOCATE @MyCursor;
END

/* Lister ut ordrene som hører til kunden etter operasjonen er kjørt */
use ZF0010
select ZF0010.ZpiderOrder.WebUserNo, ZF0010.WebUser.CustomerNo, ZpiderOrderNo, ZF0010.WebUser.Name
from ZF0010.ZpiderOrder
join ZF0010.WebUser On ZF0010.ZpiderOrder.WebUserNo= ZF0010.WebUser.WebuserNo
where  ZF0010.ZpiderOrder.WebUserNo = @NewWebuserNo  

SET @OldCustNo = @OldCustNo + 1
END

'

possible duplicate of [Subquery returned more than 1 value.This is not permitted when the subquery follows =,!=,<,<=,>,>= or when the subquery is used as an expression](http://stackoverflow.com/questions/16053907/subquery-returned-more-than-1-value-this-is-not-permitted-when-the-subquery-foll) — Tanner, Mar 19 '15 at 14:08

score 2 · Accepted Answer · answered Mar 19 '15 at 13:37

2

use.. top 1 or max()

select @NewWebuserNo = (select top 1 ZpiderOrder.WebUserNo
from ZF0010.WebUser
join ZF0010.ZpiderOrder On ZF0010.ZpiderOrder.WebUserNo = ZF0010.WebUser.WebuserNo
where CustomerNo = @OldCustNo )

OR, as @NewWebuserNo is an int number

select @NewWebuserNo = (select max(CAST(ISNULL(ZpiderOrder.WebUserNo,0) AS INT))
from ZF0010.WebUser
join ZF0010.ZpiderOrder On ZF0010.ZpiderOrder.WebUserNo = ZF0010.WebUser.WebuserNo
where CustomerNo = @OldCustNo )

answered Mar 19 '15 at 13:37

A_Sk

4,532
3
27
51

Hi this did work, it removed the error message, but i still have problems duplicating every file so i have to do some manually, and some of them only work then i am actually logged into that account – Tommy Mar 20 '15 at 08:57

Error message while duplicating purchasing lists from old database to a new database, msg 512, level 16

1 Answers1