gfk76
asked on
Help with SQL Update query
Hi Experts!
I have 2 tables with customers
Tab_Customers have the following record layout.
Customerno,
Firstname,
lastname,
adresse,
Zip,
City,
Phone,
Tab_Phone has the following recordlayout
Tab_Phone
Customerno
Firstname,
lastname,
adresse,
Zip,
City,
Phone
In Tab_Customers there is no phone numbers present
In Tab_Phone there are no customernumbers present, but it has phone numbers present.
I would like to add the customer number to the Tab_Phone table where ever there is a big similarity on the name.
I have tried the following:
update Tab_Phone
set Customerno = (
select c.Customerno from Tab_Customers c where exists(select * from Tab_Phone p where
similar (p.Firstname,c.firstname)< 99 and
similar (p.Lastname,c.lastname)<99 and
similar (p.Adresse,c.adresse)<99 and
similar (p.zip,c.zip)<99 and
similar (p.City,c.City)<99)
)
Is someone able to help me?
regards
gfk76
I have 2 tables with customers
Tab_Customers have the following record layout.
Customerno,
Firstname,
lastname,
adresse,
Zip,
City,
Phone,
Tab_Phone has the following recordlayout
Tab_Phone
Customerno
Firstname,
lastname,
adresse,
Zip,
City,
Phone
In Tab_Customers there is no phone numbers present
In Tab_Phone there are no customernumbers present, but it has phone numbers present.
I would like to add the customer number to the Tab_Phone table where ever there is a big similarity on the name.
I have tried the following:
update Tab_Phone
set Customerno = (
select c.Customerno from Tab_Customers c where exists(select * from Tab_Phone p where
similar (p.Firstname,c.firstname)<
similar (p.Lastname,c.lastname)<99
similar (p.Adresse,c.adresse)<99 and
similar (p.zip,c.zip)<99 and
similar (p.City,c.City)<99)
)
Is someone able to help me?
regards
gfk76
ASKER
Hi reb73!
Thank you for trying to help.
I should probably told you that it is a Sybase database.
Similar is a built in function in iAnywhere, that checks for similareties.
I tryed your query with similar instead of difference
update p
set Customerno = c.CustomerNo
from Tab_Phone p
inner join Tab_Customers c on
similar (p.Firstname,c.firstname)< 99 and
similar (p.Lastname,c.lastname)<99 and
similar (p.Adresse,c.adresse)<99 and
similar (p.zip,c.zip)<99 and
similar (p.City,c.City)<99)
I get following error message
"Table p not found"
Any idea?
Thank you for trying to help.
I should probably told you that it is a Sybase database.
Similar is a built in function in iAnywhere, that checks for similareties.
I tryed your query with similar instead of difference
update p
set Customerno = c.CustomerNo
from Tab_Phone p
inner join Tab_Customers c on
similar (p.Firstname,c.firstname)<
similar (p.Lastname,c.lastname)<99
similar (p.Adresse,c.adresse)<99 and
similar (p.zip,c.zip)<99 and
similar (p.City,c.City)<99)
I get following error message
"Table p not found"
Any idea?
Try changing the first line from
update p
to
update Tab_Phone
update p
to
update Tab_Phone
ASKER
This updates with the same customer number on all rows
Can you check this?
update Tab_Phone
set Tab_Phone.Customerno = Tab_Customers.CustomerNo
from Tab_Phone,Tab_Customers
where similar (Tab_Phone.Firstname,Tab_Customers.firstname)<99 and
similar (Tab_Phone.Lastname,Tab_Customers.lastname)<99 and
similar (Tab_Phone.Adresse,Tab_Customers.adresse)<99 and
similar (Tab_Phone.zip,Tab_Customers.zip)<99 and
similar (Tab_Phone.City,Tab_Customers.City)<99)
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Correction: Please change line 8 as follows -
update Tab_Phone
update Tab_Phone
ASKER
Arggghhhh.....
Thank you so much......
I have been working on this query for 1 and a half day now.....
I cant belive I missed it.
You have saved my day, and tonight my sleep :-)
Thank you so much......
I have been working on this query for 1 and a half day now.....
I cant belive I missed it.
You have saved my day, and tonight my sleep :-)
Glad to help! Cheers..
You could try the query below but it tries to match based on how the names sound when pronounced and may not be very accurate -
Open in new window