Which way should i do this? - 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)

Which way should i do this?

Author

Topic

yatesy87
Starting Member

8 Posts

Posted - 2006-01-04 : 06:04:40

Hi ive touched this topic on here a few times now but still i just cant get my head around to the solution even though I think i am quite close, below is the current code that I have:

Declare @area varchar(50)
Declare @phone varchar(50)
Declare @fullnumber varchar(50)
Declare @actual varchar(50)
Declare @counter int


set @counter=0
set @area = '01744'
set @phone = '815926'
set @actual = '01744815926'

Declare special Cursor
local scroll keyset optimistic
for
select * from data
where postalcode like 'WA9%'
open special
fetch absolute 1 from special

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

While @@fetch_status<>-1
Begin
Fetch next from special

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

set @counter=@counter+1
end
close special
deallocate special

Now Im as to a bit confused whether this is the right way to create this script. What I am doing is say in a database i have this:

PostCode: WA9???
Number: 01744656565

and another record which is:

PostCode: WA9???
Number: 656565

and another:

PostCode: WA9???
Number: 626262

Now what I need to be able to do is run the script to update the numbers which are 6 digits long with the area code which in this case is 01744 but this is where the problem is, if i update 656565 then it becomes 01744656565 which it cant do because their is already an existing number in the table what i need to do is delete the number which should be updated.

Does anybody have any ideas on how to adapt my script or another method i could use to do this?

Any help is appreciated!!!!

SamC
White Water Yakist

3467 Posts

Posted - 2006-01-04 : 07:00:49

First, throw away your cursor-based solution and start over

Sounds like you have duplicates, certainly in the short numbers, possibly in the full (area + numbers) in the database. It's probably just as easy to eliminate duplicates in both cases, but first

-- Update any numbers first to include the area code


UPDATE data
  SET number = @area + number
  WHERE postalcode LIKE 'WA9%'
  AND   LEFT(number,1) <> '0'
  AND   LEN(number) < 8

-- Delete any duplicates
-- This will require a temporary table unless you have a column with a unique value (CreateDate, ID or whatever). Assuming you have a CreateDate column...


DELETE d
  FROM data d
  LEFT OUTER JOIN (
    SELECT postalcode, number, MIN(CreateDate) as MinCreateDate
      FROM data
      GROUP BY postalcode, number
   ) e  ON   e.postalcode = d.postalcode
        AND  e.number = d.number
        AND  e.MinCreateDate = d.CreateDate
   WHERE e.number IS NULL -- Any row without a match is a duplicate

I haven't tried this. Run with test data.

shallu1_gupta
Constraint Violating Yak Guru

394 Posts

Posted - 2006-01-04 : 07:11:52

Try this ..
this will fetch you all the records which when appended with areacode results in duplicate..

select postcode+number from table where postcode+'01744'+number in( select postcode+number from table where len(number)>6)

finally u can put a delete clause

delete from table where postcode+number in (select postcode+number from table where postcode+'01744'+number in( select postcode+number from table where len(number)>6)
)
Hope this will help you

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources