Running Query Problem - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Running Query Problem

Author

Topic

yatesy87
Starting Member

8 Posts

Posted - 2005-12-16 : 04:29:50

Hi Have a script which has variables declared in it. Now basically what this script does is it checks all numbers against our crap data and if it would duplicate then it deletes it and if it doesnt exist then obviously it will update the bad number to be dialable.

When I run the script I get this error:

Server: Msg 512, Level 16, State 1, Line 0
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
Server: Msg 512, Level 16, State 1, Line 16
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
Server: Msg 512, Level 16, State 1, Line 20
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

My Script is as follows:

quote:
Set nocount on

Declare @area varchar(50)
Declare @phone varchar(50)
Declare @postalcode varchar(50)
Declare @fullnumber varchar(50)
Declare @actual varchar(50)
Declare @c int

set @area = '01744'
set @phone =
(select phonenum from data
where left(phonenum,1) <> '0'
and len(PhoneNum) < 10
)
set @postalcode =
(select postalcode from data
where postalcode like 'wa9%'
)
set @actual =
(select phonenum from data
where len(PhoneNum) > 1
)

select @C = count(*) from data
While @c > 0

IF @actual = @area + @phone
Begin
IF exists
(select * from data
where PostalCode like @postalcode
)
Delete PhoneNum from data
where left(@phone,1) <> '0'
and len(@phone) <8
and PostalCode like @postalcode
Else
update data
set PhoneNum = @area + @phone
where left(@phone,1) <> '0'
and len(@phone) <8
and PostalCode like @postalcode
End

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-12-16 : 04:38:04

It is because the subquery returns more than one value
You need to use

Select @phone =phonenum from data where left(phonenum,1) <> '0' and len(PhoneNum) < 10

The problem now is that @phone will be assigned the last returned value

Here is a good article on Deleting Duplicate records
http://sqlteam.com/forums/topic.asp?TOPIC_ID=6256

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources