Updating from other table - 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

Transact-SQL (2000)

Updating from other table

Author

Topic

aminsam
Starting Member

6 Posts

Posted - 2004-05-17 : 04:22:10

I have 2 table

table 1 have phone_number field, and cityname ,....
table 2 have code field, and cityname ,....

i want update table1.cityname base on table2.code

codes are in short format and phone_number are in long format like

phone number is 003314509456 and code is 00331 and 00336 (paris and

cell of france ) i want up date cityname in table1 first and then

what can i do for finding best match. if i couldnot find 00331 or
00336 i find 0033=france , and 0033 exist in table2)

thanks

nr
SQLTeam MVY

12543 Posts

Posted - 2004-05-17 : 05:06:23

This is similar to a telecom rating system.

declare @i int
select @i = max(len(code)) from tbl2
while @i > 0
begin
update tbl1
set cityname = tbl2.cityname
from tbl1
join tbl2
on tbl2.code = left(tbl1.phone_number,@i)
where tbl1.cityname is null
and len(tbl2.code) = @i

select @i = @i - 1
end

If you batch the number of recs this works on it should be quite efficient.
If this is rating be careful about premium rate numbers.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

aminsam
Starting Member

6 Posts

Posted - 2004-05-17 : 06:24:45

i do it.

only one city name in table1 (001x=USA-Canada) updated

and other city name is null (default value) but we have them code in table 2

nr
SQLTeam MVY

12543 Posts

Posted - 2004-05-17 : 07:20:02

There's something you aren't telling us

create table tbl1 (phone_number varchar(20), cityname VARCHAR(20))
create table tbl2 (code varchar(20), cityname VARCHAR(20))

insert tbl1 select '0111', null
insert tbl1 select '0112', null
insert tbl1 select '0122', null
insert tbl1 select '011345', null

insert tbl2 select '01', 'a'
insert tbl2 select '012', 'b'
insert tbl2 select '0112', 'c'

declare @i int
select @i = max(len(code)) from tbl2
while @i > 0
begin
update tbl1
set cityname = tbl2.cityname
from tbl1
join tbl2
on tbl2.code = left(tbl1.phone_number,@i)
where tbl1.cityname is null
and len(tbl2.code) = @i

select @i = @i - 1
end

select * from tbl1

phone_number cityname
-------------------- --------------------
0111 a
0112 c
0122 b
011345 a

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

aminsam
Starting Member

6 Posts

Posted - 2004-05-17 : 07:50:52

You are right

my fileds was char not varchar like yours

i cast the data types and problem solved

thank you very much

nr
SQLTeam MVY

12543 Posts

Posted - 2004-05-17 : 08:00:26

char for phone numbers and codes? You're wasting a lot of space unless this is just a local system.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

aminsam
Starting Member

6 Posts

Posted - 2004-05-17 : 08:03:44

yes it is local system
but anyway i mustn't waste spaces

Thanks again

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources