Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Update not working in a Stored Procedure 2

Status
Not open for further replies.
jeffwest2

jeffwest2

MIS
Feb 5, 2009
64
GB
I have the following code in a stored procedure, in essence it creates a temp table to hold some data, it then runs two update statements on a main table with details from the temp table and another table, all the values in the CIG_URN field are unique (I have already stripped out the duplicates via another SP running before this one, however I am getting the following error:-

Msg 512, Level 16, State 1, Procedure Full_Data_Load_Address, Line 54
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.
Msg 512, Level 16, State 1, Procedure Full_Data_Load_Address, Line 67
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.

I can't see why, any chance anyone can see what I am missing?

Code: 
Create table ##ClientID 
	(CIG_URN varchar(50),
	PoBox varchar(50),
	Abode varchar(50),
	BName varchar(50),
	BNumber varchar(50),
	Street1 varchar(50),
	Street2 varchar(50),
	Sublocality varchar(50),
	Locality varchar(50),
	Town varchar(50),
	Postcode varchar(50))

	Insert INTO ##ClientID (CIG_Urn,POBox,Abode,BName,BNumber,Street1,Street2,SubLocality,Locality,Town,Postcode)
	Select Distinct CIG_URN,POBox,Abode,BName,BNumber,Street1,Street2,SubLocality,Locality,Town,Postcode 
	from Call_Trace_Import where AddressLink = 'Y' and SequenceNo = '1' and CIG_URN is not NULL

	SET NOCOUNT ON;

/* First, declare items in the cursor */
DECLARE @cur_CustomerID varchar (50),
@POBox varchar (50),
@Abode varchar (50),
@BName varchar (50),
@BNumber varchar (50),
@Street1 varchar (50),
@Street2 varchar (50),
@Sublocality varchar (50),
@Locality varchar (50),
@Town varchar (50),
@Postcode varchar (50)

/* Next, declare the cursor itself */
DECLARE changes_cursor cursor
for SELECT Distinct CIG_Urn,POBox,Abode,BName,BNumber,Street1,Street2,SubLocality,Locality,Town,Postcode
FROM ##ClientID 

OPEN changes_cursor
/* first fetch starts the process */
FETCH changes_cursor INTO @cur_CustomerID,@POBox,@Abode,@BName,@BNumber,@Street1,@Street2,@Sublocality,
@Locality,@Town,@Postcode
WHILE @@fetch_status = 0 /* while there's still records to process */
BEGIN

UPDATE dbo.Call_Trace_Import
	SET Previous_Address1 = @PoBox,
		Previous_Address2 = @Abode,
		Previous_Address3 = @BName,
		Previous_Address4 = @BNumber,
		Previous_Address5 = @Street1,
		Previous_Address6 = @Street2,
		Previous_Address7 = @Sublocality,
		Previous_Address8 = @Locality,
		Previous_Address9 = @Town,
		Previous_Postcode = @Postcode
	WHERE @cur_CustomerID = (Select Distinct CIG_URN from Call_Trace_Import)

UPDATE dbo.Call_Trace_Import
	SET PoBox = (SELECT distinct n.PoBox FROM dbo.Call_Trace_Output n,Call_Trace_import c WHERE c.CIG_URN = n.CIG_URN),
		Abode = (SELECT distinct n.Abode FROM dbo.Call_Trace_Output n,Call_Trace_import c WHERE c.CIG_URN  = n.CIG_URN),
		BName = (SELECT distinct n.BName FROM dbo.Call_Trace_Output n,Call_Trace_import c WHERE c.CIG_URN  = n.CIG_URN),
		BNumber = (SELECT distinct n.BNumber FROM dbo.Call_Trace_Output n,Call_Trace_import c WHERE c.CIG_URN = n.CIG_URN),
		Street1 = (SELECT distinct n.Street1 FROM dbo.Call_Trace_Output n,Call_Trace_import c WHERE c.CIG_URN = n.CIG_URN),
		Street2 = (SELECT distinct n.Street2 FROM dbo.Call_Trace_Output n,Call_Trace_import c WHERE c.CIG_URN = n.CIG_URN),
		Sublocality = (SELECT distinct n.Sublocality FROM dbo.Call_Trace_Output n,Call_Trace_import c WHERE c.CIG_URN = n.CIG_URN),
		Locality = (SELECT distinct n.Locality FROM dbo.Call_Trace_Output n,Call_Trace_import c WHERE c.CIG_URN = n.CIG_URN),
		Town = (SELECT distinct n.Town FROM dbo.Call_Trace_Output n,Call_Trace_import c WHERE c.CIG_URN = n.CIG_URN),
		Postcode = (SELECT distinct n.Postcode FROM dbo.Call_Trace_Output n,Call_Trace_import c WHERE c.CIG_URN = n.CIG_URN) 
	WHERE @cur_CustomerID = CIG_URN  and SequenceNo = '1'

Delete from ##ClientID where CIG_URN = @cur_CustomerID

/* keep fetching till done */
FETCH changes_cursor INTO @cur_CustomerID,@POBox,@Abode,@BName,@BNumber,@Street1,@Street2,@Sublocality,
@Locality,@Town,@Postcode
END
CLOSE changes_cursor
DEALLOCATE changes_cursor

Drop Table ##ClientID

END

Didn't anyone ever tell you? There's one thing you never put in a trap — if you're smart, if you value your continued existence, if you have any plans about seeing tomorrow — there's one thing you never — ever, put in a trap. … Me.
 
Sort by date Sort by votes
Sorry forgot to mention i am using SQL express 2005

Didn't anyone ever tell you? There's one thing you never put in a trap — if you're smart, if you value your continued existence, if you have any plans about seeing tomorrow — there's one thing you never — ever, put in a trap. … Me.
 
Upvote 0 Downvote
Its probably this bit (test first)

@cur_CustomerID = (Select Distinct CIG_URN from Call_Trace_Import)


should be

@cur_CustomerID in (Select Distinct CIG_URN from Call_Trace_Import)

You cannot say = when you are dealing with multiple values.

You may also need to change the ones like this

PoBox = (SELECT distinct n.PoBox FROM dbo.Call_Trace_Output n,Call_Trace_import c WHERE c.CIG_URN = n.CIG_URN),

to this

PoBox = (SELECT max(n.PoBox) FROM dbo.Call_Trace_Output n,Call_Trace_import c WHERE c.CIG_URN = n.CIG_URN),


----------------------------------------

Be who you are and say what you feel, because those who mind don't matter and those who matter don't mind - Bernard Baruch

Computer Science is no more about computers than astronomy is about telescopes - EW Dijkstra
----------------------------------------
 
Upvote 0 Downvote

Your issue could be with any of the update selects, you need to ensure they can only return 1 result.

Chances are you have a coule one which are returning 2 values and the procedure won;t know which one to use to do your update with.

I love deadlines. I like the whooshing sound they make as they fly by
Douglas Adams
(1952-2001)
 
Upvote 0 Downvote
Thanks SQLScholar and Dhulbert, rellooking at my code it was the select statements for the data being updated.

No re-looking at it with those two thought in mind I have solved it, thanks to you both.

Code: 
	Create table ##ClientID 
	(CIG_URN varchar(50),
	PoBox varchar(50),
	Abode varchar(50),
	BName varchar(50),
	BNumber varchar(50),
	Street1 varchar(50),
	Street2 varchar(50),
	Sublocality varchar(50),
	Locality varchar(50),
	Town varchar(50),
	Postcode varchar(50))

	Insert INTO ##ClientID (CIG_Urn,POBox,Abode,BName,BNumber,Street1,Street2,SubLocality,Locality,Town,Postcode)
	Select Distinct CIG_URN,POBox,Abode,BName,BNumber,Street1,Street2,SubLocality,Locality,Town,Postcode 
	from Call_Trace_Import where AddressLink = 'Y' and SequenceNo = '1' and CIG_URN is not NULL

	SET NOCOUNT ON;

/* First, declare items in the cursor */
DECLARE @cur_CustomerID varchar (50),
@POBox varchar (50),
@Abode varchar (50),
@BName varchar (50),
@BNumber varchar (50),
@Street1 varchar (50),
@Street2 varchar (50),
@Sublocality varchar (50),
@Locality varchar (50),
@Town varchar (50),
@Postcode varchar (50)

/* Next, declare the cursor itself */
DECLARE changes_cursor cursor
for SELECT Distinct CIG_Urn,POBox,Abode,BName,BNumber,Street1,Street2,SubLocality,Locality,Town,Postcode
FROM ##ClientID 

OPEN changes_cursor
/* first fetch starts the process */
FETCH changes_cursor INTO @cur_CustomerID,@POBox,@Abode,@BName,@BNumber,@Street1,@Street2,@Sublocality,
@Locality,@Town,@Postcode
WHILE @@fetch_status = 0 /* while there's still records to process */
BEGIN

UPDATE dbo.Call_Trace_Import
	SET Previous_Address1 = (Select POBox from Call_Trace_Import where CIG_URN = @cur_CustomerID and POBox = @PoBox),
		Previous_Address2 = (Select Abode from Call_Trace_Import where CIG_URN = @cur_CustomerID and Abode = @Abode),
		Previous_Address3 = (Select BName from Call_Trace_Import where CIG_URN = @cur_CustomerID and BName = @BName),
		Previous_Address4 = (Select BNumber from Call_Trace_Import where CIG_URN = @cur_CustomerID and BNumber = @BNumber),
		Previous_Address5 = (Select Street1 from Call_Trace_Import where CIG_URN = @cur_CustomerID and Street1 = @Street1),
		Previous_Address6 = (Select Street2 from Call_Trace_Import where CIG_URN = @cur_CustomerID and Street2 = @Street2),
		Previous_Address7 = (Select Sublocality from Call_Trace_Import where CIG_URN = @cur_CustomerID and Sublocality = @Sublocality),
		Previous_Address8 = (Select Locality from Call_Trace_Import where CIG_URN = @cur_CustomerID and Locality = @Locality),
		Previous_Address9 = (Select Town from Call_Trace_Import where CIG_URN = @cur_CustomerID and Town = @Town),
		Previous_Postcode = (Select Postcode from Call_Trace_Import where CIG_URN = @cur_CustomerID and Postcode = @Postcode)
	WHERE @cur_CustomerID = CIG_URN 


UPDATE dbo.Call_Trace_Import
	SET PoBox = (SELECT n.PoBox FROM dbo.Call_Trace_Output n where CIG_URN = @cur_CustomerID),
		Abode = (SELECT n.Abode FROM dbo.Call_Trace_Output n where CIG_URN = @cur_CustomerID),
		BName = (SELECT n.BName FROM dbo.Call_Trace_Output n where CIG_URN = @cur_CustomerID),
		BNumber = (SELECT n.BNumber FROM dbo.Call_Trace_Output n where CIG_URN = @cur_CustomerID),
		Street1 = (SELECT n.Street1 FROM dbo.Call_Trace_Output n where CIG_URN = @cur_CustomerID),
		Street2 = (SELECT n.Street2 FROM dbo.Call_Trace_Output n where CIG_URN = @cur_CustomerID),
		Sublocality = (SELECT n.Sublocality FROM dbo.Call_Trace_Output n where CIG_URN = @cur_CustomerID),
		Locality = (SELECT n.Locality FROM dbo.Call_Trace_Output n where CIG_URN = @cur_CustomerID),
		Town = (SELECT n.Town FROM dbo.Call_Trace_Output n where CIG_URN = @cur_CustomerID),
		Postcode = (SELECT n.Postcode FROM dbo.Call_Trace_Output n where CIG_URN = @cur_CustomerID) 
	WHERE @cur_CustomerID = CIG_URN 

Delete from ##ClientID where CIG_URN = @cur_CustomerID

/* keep fetching till done */
FETCH changes_cursor INTO @cur_CustomerID,@POBox,@Abode,@BName,@BNumber,@Street1,@Street2,@Sublocality,
@Locality,@Town,@Postcode
END
CLOSE changes_cursor
DEALLOCATE changes_cursor

Drop Table ##ClientID

END

Didn't anyone ever tell you? There's one thing you never put in a trap — if you're smart, if you value your continued existence, if you have any plans about seeing tomorrow — there's one thing you never — ever, put in a trap. … Me.
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

Cpreston
  • Locked
  • Question
Working Days giving NULL values
Replies
8
Views
226
Cpreston
Cpreston
Cpreston
  • Locked
  • Question
Update with a month name 1
Replies
9
Views
376
gmmastros
gmmastros
leifoet
  • Locked
  • Question
How can I update the input of a form (rows and columns) to the database in one query ?
Replies
1
Views
151
Chris Miller
Chris Miller
leifoet
  • Locked
  • Question
Error in Select query
Replies
14
Views
289
Andrzejek
Andrzejek
TheBugSlayer
  • Locked
  • Question
Transaction with SELECT and UPDATE with XLOCK causing deadlocks 2
Replies
10
Views
357
gmmastros
gmmastros

Part and Inventory Search

Sponsor

Back
Top