Need to Overwrite - 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)

Need to Overwrite

Author

Topic

yatesy87
Starting Member

8 Posts

Posted - 2005-12-04 : 08:06:31

Hi

Im having a bit of a probelm, at the moment i have a query which is as follows:

update tbl
set PhoneNum '01744' + PhoneNum
Where left(PhoneNum,1) <> '0'
and len(PhoneNum) <10
and PostCode like 'WA9%'

what this does is the data in our tables which dont have an area code then basically this script will place it in for us.

The problem that im having is that some data is duplicated in a way ... for example to numbers might have the same postcode and be the following:

01744564564
564564

so basically when i try to run my script that data already exists so i would get an error message for duplicating. Is their any way that I can get around this to overwrite the existing data or if it already exists then just leave it.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2005-12-04 : 09:09:23

[code]create table #tbl
(
PhoneNum varchar(20),
PostCode varchar(10),
primary key (PhoneNum)
)

delete #tbl
insert into #tbl (PhoneNum, PostCode)
select '564564', 'WA91' union all
select '564565', 'WA91' union all
select '01744564564', 'WA91' union all
select '123456', 'WA81'

update #tbl
set PhoneNum = '01744' + PhoneNum
from #tbl t
Where left(PhoneNum,1) <> '0'
and len(PhoneNum) <10
and PostCode like 'WA9%'
and not exists (select * from #tbl x where x.PhoneNum = '01744' + t.PhoneNum)[/code]

-----------------
[KH]

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2005-12-04 : 11:41:44

Why does your phone number have a unique constraint or index? There are five people in my house, and we all have the same phone number...
Maybe you need to rethink your design.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources